Script-Question

[xyz_1011]

Hi all,

i have the following data structure:

Source:

Part_ID	Production_Run_From	Production_Run_To	Price
1234	1	3	100
1234	4	5	200
5678	1	10	10000
9876	1	4	500

In my script, i need / want to transform the data into the following structure:

Target:

Part_Id	Production_Run_1	Production_Run_2	Production_Run_3	Production_Run_4	Production_Run_5
1234	100	100	100	200	200
5678	10000	10000	10000	10000	10000
9876	500	500	500	500	--

(Note, that i need to limit the data in my target table to the first five runs only.)

SInd failing desparately trying to do this with loops in loops i wonder, if someone here can give me some help. Thanks a lot in advance!

[Or · Accepted solution]

There's probably a way to do this in one step by using IterNo() to set the field name, but it should be easy in two:

Table1:

Load PartID, Production_Run_From + IterNo() - 1 as RunNumber, Price

From YourTable

While Production_Run_From + IterNo() -1  <= Production_Run_To;

Load PartID, Price as Production_Run_1

Resident Table1

Where RunNumber = 1 ;

Concatenate

Load PartID, Price as Production_Run_2

Resident Table1

Where RunNumber = 2;

etc.

[Edited to add:] As per Maxgrow's suggestion, you can indeed replace the bottom part of this with a generic load and it'll be cleaner.

[maxgro]

maybe with a generic load, you can find an explanation here 

https://community.qlik.com/t5/Design/The-Generic-Load/ba-p/1473470

// source
T:
load * inline [
Part_ID, Production_Run_From, Production_Run_To, Price
1234, 1, 3, 100
1234, 4, 5, 200
5678, 1, 10, 10000
9876, 1, 4, 500
];

T2:
load
Part_ID,
'Production_Run_' & (Production_Run_From + iterno()-1) as Production_Run,
Price
Resident T
while
Production_Run_From + iterno()-1 <= Production_Run_To
AND IterNo() <= 5;

drop table T;

T3:
Generic LOAD Part_ID, Production_Run, Price Resident T2;

DROP Table T2;