Multi-Row formula

[Sarath27]

Hi All, 

Pls assist me on this scenarios. Basically each product will have three different levels (i.e Level 1, Level2 & Level 3).

Scenario 1: If 2 out of 3  levels has values populated in it, then we need to create two duplicate line and split the values in both the lines.

Scenario 2 : If 3 out 3 levels has values populated in it, then we need to create three duplicate lines and split the value in each lines. 

Note: Splitting values can be in any level, but it should be unique.

Scenario 1
Input
Product	Level 1	Level 2	Level 3
A	1	1	0
Desired output
Product	Level 1	Level 2	Level 3
A	1	0	0
A	0	1	0

Scenario 2
Input
Product	Level 1	Level 2	Level 3
A	1	1	1
Desired output
Product	Level 1	Level 2	Level 3
A	1	0	0
A	0	1	0
A	0	0	1

[Prometheus · Accepted solution]

@Sarath27 I was able to solve without using a Multi-Row formula tool. First, I used the Transpose tool to pivot the data, a RecordID tool to give the records something else to group by, otherwise the data would've been summed at this point and it would've been set back to its original form after the next tool, with is the Cross Tab tool. The Cross Tab tool pivots the data back, but leaves nulls where there is no data, so I replaced those nulls with zeroes using the Multi-Field Formula tool. I then added all the Level fields together and only allowed records to pass through a Filter tool if Total Value > 0. After using a Dynamic Rename tool to replace the "_" in field names with a space, I deselected unnecessary fields and sorted on Product ascending to get what you're looking for.

 

 

 

 

 

 

[Sarath27]

@Prometheus Amazing solution! 100% working!!! Thanks much.