Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Help: Count consecutive number of columns with zero's per row

RahulThakrar
5 - Atom

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!

7 REPLIES 7
jdunkerley79
ACE Emeritus
ACE Emeritus

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.

 

2017-04-21_11-57-08.jpg

 

Sample attached

James

 

 

SeanAdams
17 - Castor
17 - Castor
Hey Rahul,
This is very achievable - I've written out an approach below but happy to mock it up for you later if you need.

- put a unique row ID on each row if you don't already have one using the record ID tool
- use the transpose tool to flip from columns to rows, except for the record ID
- then use a multi-row formula, groping by the record ID and setting the treatment of missing rows to null not zero.
The multi row formula would be something like
If isnull (row-1:value) and value=0
Then 1
Else if value !=0 then 0
Else row-q:value+1
Endif

Happy to mock this up if you need
SeanAdams
17 - Castor
17 - Castor
:-) I like James solution better than my own!

I'll mock up the multi-field formula version, and also an adaptation of James's solution using the tile tool when I get home
RahulThakrar
5 - Atom

Worked perfectly fine! Thanks a lot!

SeanAdams
17 - Castor
17 - Castor

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:

Capture1-multiRow.PNG

 

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)

 

Tile Method.PNG

 

 

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.

 

GenerateTestData.PNG

Austin004lasrado
7 - Meteor

Hello @jdunkerley79  and @SeanAdams , I have a slightly different requirement. where instead of counting Null values, i need to count the number of consecutive non null values of each sales person has on monthly basis. I get values as below

Group Sales 01-03-202301-04-202301-05-202301-06-202301-07-2023CommentsStatus
ABC25773523.6724226487.2515927363.8921940141.721320070.2IgnoreActive
DEF   6721623.6447421914.188XYZActive
GHI     XYZInactive
JKL 154963.3463136503.9697241804.4598305480.9466XYZActive
MNO  683249.6127  XYZInactive
PQR1254801.5931620222.866   XYZInactive
STU661496.60721552791.264 2215380.82083746.769XYZActive
VWX10122977.9210811831.18   XYZInactive
YZ1175610.5636 147653.1286  XYZInactive

 

Here i have to count consecutive values( before null) from Numeric fields across columns . also these fields are dynamic where every months value keep on adding in a new column so can you please provide a solution to this problem.

 

I need a result like below.

Group Sales 01-03-202301-04-202301-05-202301-06-202301-07-2023CountCommentsStatus
ABC25773523.6724226487.2515927363.8921940141.721320070.25IgnoreActive
DEF   6721623.6447421914.1882XYZActive
GHI     0XYZInactive
JKL 154963.3463136503.9697241804.4598305480.94664XYZActive
MNO  683249.6127  0XYZInactive
PQR1254801.5931620222.866   0XYZInactive
STU661496.60721552791.264 2215380.82083746.7692XYZActive
VWX10122977.9210811831.18   0XYZInactive
YZ1175610.5636 147653.1286  0XYZInactive

 

 

 

SeanAdams
17 - Castor
17 - Castor

Hey @Austin004lasrado ,

 

 

This is a special case of the same problem - here's one way to tackle this:

 

- Transpose the data keeping the Group Sales column - that way you will have the group sales column, a name column and a value column.

- Sort by group sales; name

- add in a multi-row tool which groups by Group sales, and then creates a 1 if the value is non-null or else a zero if the prev row is zero or null - call this "MyCounter"

- add in a summarize tool - group by Group Sales, and sum up the "MyCounter" field

- then join this new column back to your original data set

 

Done!

Labels