Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
xyz_1011
Partner - Creator II
Partner - Creator II

Script-Question

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!

Labels (1)
1 Solution

Accepted Solutions
Or
MVP
MVP

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.

 

View solution in original post

2 Replies
Or
MVP
MVP

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
MVP
MVP

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;

 

maxgro_0-1693822924155.png