Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
thanks a lot Anand an Rob. That really helped me out.
Regards
Daniel