Problem removing zero values from a list and maintaining duplicates

[Patrick_Paul]

I have a dynamically generated list column on each record in a table.  It consists of decimal values.  I need to count all the non-zero elements in the list.  The problem is that if I subtract a list of zero (-list(0)), it has the side effect of eliminating any duplicate values in the list as well (it effectively makes the list only contain unique values), which is not desirable because then those duplicate elements don't get counted.  I cannot use filter() or select() to achieve this because the list is comprised of columns from within each row.  Help?

[Suvrutt_Gurjar · Accepted solution]

Could you share how you are constructing the list?

is it as follows, since you mentioned you are creating it from the same record?

LIST([Column 1] , [Column 2]........., [Column n-1], [Column n])

Also could you mention what you mean by dynamically created list?

[Suvrutt_Gurjar · Accepted solution]

Could you share how you are constructing the list?

is it as follows, since you mentioned you are creating it from the same record?

LIST([Column 1] , [Column 2]........., [Column n-1], [Column n])

Also could you mention what you mean by dynamically created list?

[Patrick_Paul]

"is it as follows, since you mentioned you are creating it from the same record?

LIST([Column 1] , [Column 2]........., [Column n-1], [Column n])"

Yes.

Dynamically in that it's a virtual column created on the fly.

[Suvrutt_Gurjar · Accepted solution]

Thank you.

It is not very elegant but I would try in another VC, an expression something like below which would give me the count of non zero columns in the list.

IF([Column 1]>0 , 1, 0)+
IF([Column 2]>0 , 1, 0)+

........+
IF([Column n-1]>0 , 1, 0)+
IF([Column n ]>0 , 1, 0)

[MultiTech]

This is a known limitation of the platform:  when you perform list subtraction, it also performs a silent `unique()` on the list as well.

We have requested, many times... so many times.... that they NOT do this - we have UNIQUE() that we can use for this if we want to reduce the list to simplest terms;  but there are more times when I actually do NOT want to do that (in fact, removing duplicates would cause problems).

• The solution is different for each, requiring a work around for every situation - usually a convoluted work around at that.

---

@Suvrutt_Gurjar wrote:

It is not very elegant but I would try in another VC, an expression something like below which would give me the count of non zero columns in the list.

IF([Column 1]>0 , 1, 0)+
IF([Column 2]>0 , 1, 0)+

---

This is how I've solved problems like this too. 😞 

[Patrick_Paul]

Yaaaaay.  I was afraid that was the case, but there's no obvious documentation on the phenomenon I could find.  I'll use Suvrutt's suggestion, I was hoping not to resort to something brute force like that.