topic Re: Splitting IDs and assign numbers to first ID in App Development

Splitting IDs and assign numbers to first ID

waleeed_mahmood — Sat, 16 Nov 2024 18:05:37 GMT

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.

Re: Splitting IDs and assign numbers to first ID

sunny_talwar — Thu, 17 Sep 2020 18:08:49 GMT

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 ];

Re: Splitting IDs and assign numbers to first ID

waleeed_mahmood — Thu, 17 Sep 2020 20:55:17 GMT

Thank you @sunny_talwar