Hi, some help with the following problem would be super
I have a multitude of rows, each one with 36 columns (one per month, for three years). Each one of them start with a 0 in the first column and then eventually have a non-zero in different columns (some in March, others in September, etc). I need to count the consecutive number of columns (i.e. months) that each row stays at zero
Any idea on how best to go about this? Thanks!
Solved! Go to Solution.
So I would use a Field Info tool to get a list of the column names and then use this to assign a column index number (using a Record ID tool).
Next use a Transpose tool to convert from columns to rows
You can then join to the output of the Field Info tool to give a column Index for this row-based data.
Next we can filter out the zero records (Filter tool) and find the minimum column index for each row (using a Summarise tool).
The number of zeros leading to this is then just this index minus 1.
Sample attached
James
Worked perfectly fine! Thanks a lot!
Few more options for you Rahul (so that you can see how in Alteryx there are often multiple ways to solve the same problem.
Note: While I was testing this, I found an edge case had not yet been built into James's model, which is where every month is zero. It's not that James's solution is wrong at all, but just to be safe you can add a join back to the entites list (I've done this below in the solution using the Tile mode)
so - two different approaches to solve this (when you add in James's solution above - you now have 3 ways to tackle)
Using a multi-row formula:
Tile Method
Using @jdunkerley79's method of taking a minimum of the non-zero dates - but I've done it here using a Tile tool (which is suprisingly good for generating IDs)
Test DAta Generation: Oh- and along the way I had to generate some test data to test this - so built a quick random-test-data generator for this use-case.