Generate different number of rows based on different conditions

[AkisM]

I have a table that looks like this 

Name	Team	Condition1	Condition2
John	A	30	0
John	A	15	17
John	A	5	0
Bob	A	5	0
Bob	A	8	0
Bob	B	0	45
Bob	B	26	0
Bob	B	47	0

I would like to generate 1 or more rows, based on how many of conditions1 to x are satisfied, for each different testing group.  A testing group is defined as where the name is the same but the team is different, or where the team is the same but the name is different (or where both change, such as another 3 rows of John with team C). So the first 3 rows are 1 group, The next 2 are another group, and the last 3 are another group. If the first group for example has any row where condition1 isn't 0, then 1 row should be added to that group. If the first group has any row where condition2 isn't 0, then another row should be added to that group. So each group may get an extra 1 or 2 rows.

With these rules, the above table should look like this:

Name	Team	Condition1	Condition2
John	A	30	0
John	A	15	17
John	A	5	0
John	A	Null	Null
John	A	Null	Null
Bob	A	5	0
Bob	A	8	0
Bob	A	Null	Null
Bob	B	0	45
Bob	B	26	0
Bob	B	47	0
Bob	B	Null	Null
Bob	B	Null	Null

Any ideas? I know how to get the groupping done, (used sum tool, group by name and group by team, then add record id, then join back to original data by name and group), but not really sure how to do the row testing and generation.

[Jonathan-Sherman · Accepted solution]

Hi @AkisM,

I believe this solves the issue you were trying to achieve?

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[AkisM]

Hi @Jonathan-Sherman , I forgot to mention that there are also negative values in the tests. The max method doesn't account for negative values (the condition should still be true if a test has a negative value since it's not 0)

Edit: Fixed by just adding min, and editing formula to say "or min<0". Thanks

[Jonathan-Sherman]

Hi @AkisM,

In that case you could use an absolute function ABS() in front of the summarise tools when calculating the extra rows. The originating data would still remain negative if negative before (such as in my example -17)

If this solves your issue please mark the answer as correct, if not let me know! I've attached an updated workflow for you to download if needed.

Regards,

Jonathan