Loop in LOAD Script transforming table

[#1]

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:

ID	Date	Duration_Type	Duration
103275	08.01.15	a	0,15
103275	08.01.15	b	2,3
103275	08.01.15	e	0,78
103275	09.01.15	a	5,1
103275	09.01.15	c	4,2
103275	09.01.15	d	0,12
103275	10.01.15	a	0,58
103275	10.01.15	b	2,75
103275	10.01.15	c	1,3
103275	10.01.15	d	0,54
103275	10.01.15	e	0,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:

ID	Date	DT_a	DT_b	DT_c	DT_d	DT_e
103275	08.01.15	0,15	2,3	-	-	-
103275	09.01.15	5,1	-	4,2	0,12	-
103275	10.01.15	0,58	2,75	1,3	0,54	0,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.

[rwunderlich · Accepted solution]

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

Regards

Anand

[#4]

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

Regards

Daniel