Splitting IDs and assign numbers to first ID

[waleeed_mahmood]

Hello,

I hope you are doing well.

My question is as follows:

As shown below, i have a table (Source Table) that i have to load into QS. However, the ID column has multiple IDs in it and we want to split that. However, if i split it, the targets also get split and we have duplicates. Is there a way to have the source table where the targets/actuals are only assign to 1 ID (doesnt matter which one) and the rest are 0.

Source Table:

Customer	ID	Targets	Actuals
C1	C1_ID1,C1_ID2,C1_ID3	2500000	4500000
C2	C2_ID1,C2_ID2,C2_ID3	321000	423000

Result Table:

Customer	ID	Targets	Actuals
C1	C1_ID1	2500000	4500000
C1	C1_ID2	0	0
C1	C1_ID3	0	0
C2	C2_ID1	321000	423000
C2	C2_ID2	0	0
C2	C2_ID3	0	0

Please ask if you have any questions.

Thank you for your help. 

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD Customer,
	 SubField(ID, ',') as ID,
	 If(AutoNumber(RowNo(), Customer) = 1, Targets, 0) as Targets,
	 If(AutoNumber(RowNo(), Customer) = 1, Actuals, 0) as Actuals;
LOAD * INLINE [
    Customer, ID, Targets, Actuals
    C1, "C1_ID1,C1_ID2,C1_ID3", 2500000, 4500000
    C2, "C2_ID1,C2_ID2,C2_ID3", 321000, 423000
];

[waleeed_mahmood]

Thank you @sunny_talwar