Designer - counting zeros

[shashank0289]

Hi

I have 7 columns with values ranging from 0-5

0	0	1	1	2
0	1	2	2	3
0	0	0	0	0

I need to identify the column, for each row, whenever the value changes from 0 to something else in the next column.

the value is always zero (0) in the first column and then can change in the subsequent columns. In some cases all values might be zero (0).

I solved this in excel, by counting the number of zeros in each row, and using that value as an index.

I think it can be done in Alteryx using a nested-if, but is there any simpler solution?

Thanks in advance

[IraWatt · Accepted solution]

Hey @shashank0289,

Interesting problem, you could definitely do a nested IF. Another way is to pivot the data onto one line then filter out the non-zeros taking the first line for each row. Heres an example of this:

This solution uses the transpose tool which can be hard to understand to new users. I would recommend checking out the community Getting-Started-Learning-Path for some context on how it works: https://community.alteryx.com/t5/Learning-Paths/Getting-Started-Learning-Path/ta-p/475117#done

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[shashank0289]

Thanks for the solve, really appreciate it

I'm familiar with the transpose tool, but will try to understand it better!

[IraWatt]

No worries @shashank0289, great to hear! Just a general rule if you need to write 5+ if statements checking each column you probably just want to transpose your data and apply the logic on one then pivot back.