Splitting single row into 2 based on a column

[Jasdev]

Hi.

I have a scenario for which I have been thinking from a lot time but couldn't figure out how it can be done. 

My input looks like

col1	col2	col3	col4	col5
a			1	2
b	3	4
c
d	5	6	7	8

My output should look like

col1	op2	op3
a	1	2
b	3	4
c
d	5	6
d	7	8

I can figure out a way where either of col2, col3 or col4 col5 are populated, i can use if else to get output. I am not understanding the scenario of splitting col1 where all col2,3,4,5 are populated so I have to split them in 2 different rows. Here, col2 and col3 and one set like cost and quantity for one scenario and col4 and col5 are cost and quantity of different scenario so theyll have values together or will be 0 together. Is there a way to generate the rows completely and split these 2 different scenarios? 

[DavidP]

How important is empty row c? This deals with everything except row c.

[fmvizcaino]

Hi @Jasdev ,

Example attached.

Take a look and let me know if this makes sense to you. 🙂

Best,

Fernando Vizcaino

[Jasdev]

Hi @fmvizcaino 

I used this workflow and it works but frankly i still need to understand what is happening here. I also wanted to ask you if there is any other way to generate a new column saying "type 1" or "type 2" based on if the value is coming from col3, col4 / col5,col6.

Basically if col3 and col4 only has values it will say type 1

col4 col 5 has values it will say type 2

if both have them then when it split it into 2 rows, it says 1st row where value are from 1st set as type 1 and other as type 2. Where neither have value, it can leave empty or maybe say null. 

[fmvizcaino]

Hi @Jasdev ,

I've attached a second version of my example for you. I've removed the tiles since it was doing absolutely nothing hahahaha I've also comment some tools to give you information about my whole idea.

Best,

Fernando Vizcaino

[Jasdev]

Hi @fmvizcaino  thanks. I tested this workflow and it doesnt seems to work succesfully when there are more than 1 reference columns like where I have column 1, assume it to be like 5-7 other columns. Col 4,5,6,7 remains same and column "type" remains as is.

The issue that is happening is, for lines having value in col4,5,6,7 doesn't split in 2 lines for me. Something like this: The last line doesn't split into 2.

Column 1	Column 2	Column 3	Column 4	Column 5	quantity type1	cost type1	quantity type2	cost type2
Ram	Unassigned	12345	Indus	2
Shyam	Unassigned	72637	Ret	20			1	5
Gagan	Unassigned	46192	Indus	6	1	10
Jagan	Unassigned	13842	Prop Mgmt	6	1	15	1	20

[Jasdev]

hi @fmvizcaino  any updates?

[TomWelgemoed]

Hi @Jasdev ,

Can you confirm what the last line should look like? I know I'm just joining the party 🙂 but your explanation of what rows should stay on the same line in the final data example isn't 100% clear to me.

Thanks,

Tom

[Jasdev]

Hi @TomWelgemoed 

Thanks. Please find below examples of input and output that may make more sense. Input will be:

olumn 1	Column 2	Column 3	Column 4	Column 5	quantity type1	cost type1	quantity type2	cost type2
Ram	Unassigned	12345	Indus	2
Shyam	Unassigned	72637	Ret	20			1	5
Gagan	Unassigned	46192	Indus	6	1	10
Jagan	Unassigned	13842	Prop Mgmt	6	1	15	1	20

Output should split all those rows where both (quantity type 1, cost type 1) and  (quantity type 2 cost type 2) has values or both are zero. Output should look like

olumn 1	Column 2	Column 3	Column 4	Column 5	quantity	cost	type
Ram	Unassigned	12345	Indus	2			1
Ram	Unassigned	12345	Indus	2			2
Shyam	Unassigned	72637	Ret	20	1	5	2
Gagan	Unassigned	46192	Indus	6	1	10	1
Jagan	Unassigned	13842	Prop Mgmt	6	1	15	1
Jagan	Unassigned	13842	Prop Mgmt	6	1	20	2

[TomWelgemoed]

Thanks - I'll work with this and ping you back once done.