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

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!

Common Use Cases

Accepted answers

jdunkerley79

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

Number of Zeros.yxmd

All comments

jdunkerley79

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

Number of Zeros.yxmd

SeanAdams

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

:-) 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

Worked perfectly fine! Thanks a lot!

Austin004lasrado

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-2023	01-04-2023	01-05-2023	01-06-2023	01-07-2023	Comments	Status
ABC	25773523.67	24226487.25	15927363.89	21940141.7	21320070.2	Ignore	Active
DEF				6721623.644	7421914.188	XYZ	Active
GHI						XYZ	Inactive
JKL		154963.3463	136503.9697	241804.4598	305480.9466	XYZ	Active
MNO			683249.6127			XYZ	Inactive
PQR	1254801.593	1620222.866				XYZ	Inactive
STU	661496.6072	1552791.264		2215380.8	2083746.769	XYZ	Active
VWX	10122977.92	10811831.18				XYZ	Inactive
YZ1	175610.5636		147653.1286			XYZ	Inactive

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-2023	01-04-2023	01-05-2023	01-06-2023	01-07-2023	Count	Comments	Status
ABC	25773523.67	24226487.25	15927363.89	21940141.7	21320070.2	5	Ignore	Active
DEF				6721623.644	7421914.188	2	XYZ	Active
GHI						0	XYZ	Inactive
JKL		154963.3463	136503.9697	241804.4598	305480.9466	4	XYZ	Active
MNO			683249.6127			0	XYZ	Inactive
PQR	1254801.593	1620222.866				0	XYZ	Inactive
STU	661496.6072	1552791.264		2215380.8	2083746.769	2	XYZ	Active
VWX	10122977.92	10811831.18				0	XYZ	Inactive
YZ1	175610.5636		147653.1286			0	XYZ	Inactive

SeanAdams

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!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703