Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loop in LOAD Script transforming table

Im a newbie to qlikview and could need some help. I`ve already learned a lot from in this forum but right now im facing a problem.

Every week I get a table similar tot he example table. It contains for every ID all different duration_Types an the durtion.

Example Input:

    

IDDateDuration_TypeDuration
10327508.01.15a0,15
10327508.01.15b2,3
10327508.01.15e0,78
10327509.01.15a5,1
10327509.01.15c4,2
10327509.01.15d0,12
10327510.01.15a0,58
10327510.01.15b2,75
10327510.01.15c1,3
10327510.01.15d0,54
10327510.01.15e0,76

I want to transform the table with a single record for every ID on a date and for every possible duration an own column.

Example Output:

     

IDDateDT_aDT_bDT_cDT_dDT_e
10327508.01.150,152,3---
10327509.01.155,1-4,20,12-
10327510.01.150,582,751,30,540,76

As I get every week a new input table with about 6000 records and 85 different duration types a hint for a loop-script would be fantastic. I’ve already tried a FOR NEXT loop and a left join looping through all the different dates, Id´s and Duration_Types, but it didn´t work. Learned a lot about loads loops variables......and at the end that this was the wrong approach Perhaps someone could offer me a hint how to solve this or a script fort he example data.

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

You can do this with GENERIC LOAD. Roughly,

GENERIC LOAD

ID,

'DT_' & Duration_Type,

Duration

RESIDENT ...


See Use cases for Generic Load | Qlikview Cookbook for an example. The QV Ref Guide also has a good example in section 27.1.


-Rob

http://masterssummit.com

http://robwunderlich.com


View solution in original post

3 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

You can do this with GENERIC LOAD. Roughly,

GENERIC LOAD

ID,

'DT_' & Duration_Type,

Duration

RESIDENT ...


See Use cases for Generic Load | Qlikview Cookbook for an example. The QV Ref Guide also has a good example in section 27.1.


-Rob

http://masterssummit.com

http://robwunderlich.com


its_anandrjs

Hi,

Try this also i extend Robs Script.

Generic LOAD

ID, Date,

'DT_' & Duration_Type,

Duration

Resident Source;

DROP Table Source;

Or

Generic LOAD

ID&'_'&Date,

'DT_' & Duration_Type,

Duration

Resident Source;

DROP Table Source

Output.PNG

Regards

Anand

Not applicable
Author

thanks a lot Anand an Rob. That really helped me out.

Regards

Daniel