
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Generic load with two dimensions?
Hello,
I've previously done generic loads as described here: The GENERIC Load – QLIKIES
However, in my current project I am required to transform table 1 below, into table 2. I'm unable to achieve this with the standard generic load statement, as there are two dimensions (Definition and Time). Are there any other ways to achieve this transformation?
Table 1:
Key | Definition | Time | Result |
1 | Weight | 1/1/2017 1:00 | 54 |
1 | Weight | 1/2/2017 1:00 | 50 |
1 | Blood pressure | 1/1/2017 1:00 | 80 |
1 | Blood pressure | 1/1/2017 13:00 | 68 |
1 | Blood pressure | 1/2/2017 1:00 | 70 |
1 | Heart Rate | 1/1/2017 1:00 | 68 |
1 | Heart Rate | 1/2/2017 1:00 | 54 |
2 | Weight | 1/15/2017 4:00 | 78 |
2 | Blood pressure | 1/15/2017 4:00 | 87 |
2 | Blood pressure | 1/16/2017 4:00 | 90 |
2 | Blood pressure | 1/17/2017 4:00 | 84 |
2 | Blood pressure | 1/18/2017 4:00 | 83 |
2 | Heart Rate | 1/15/2017 4:00 | 88 |
Table 2:
Key | Weight | Blood Pressure | Heart Rate | Time |
1 | 54 | 80 | 68 | 1/1/2017 1:00 |
1 | - | 68 | - | 1/1/2017 13:00 |
1 | 50 | 70 | 54 | 1/2/2017 1:00 |
2 | 78 | 87 | 88 | 1/15/2017 4:00 |
2 | - | 90 | - | 1/16/2017 4:00 |
2 | - | 84 | - | 1/17/2017 4:00 |
2 | - | 83 | - | 1/18/2017 4:00 |
hic

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Manas, one easy way seems to create a composite key using both fields, so you end with only one key and you can apply the usual solution, you can split it later using subfield():
LOAD Key&'#'& Time as CompositeKey,
....
LOAD Subfield(CompositeKey, '#', 1) as Key,
Subfield(CompositeKey, '#', 2) as Time,
...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I wonder, Why this need script i meant to say while load. One front end solution may be this?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Are you sure, by help of Composite key can we achieve this? If so, Can you help me to create script. I've done something like this.
LOAD *, SubField(CKey, '#', 1) as Key, SubField(CKey, '#', 2) as Time;
LOAD Definition, Key & '#' & Time as CKey, Result
FROM ...;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Manas,
Generic
LOAD Key,Time,
Definition,Result
FROM
"https://community.qlik.com/message/1303333"
(html, codepage is 1252, embedded labels, table is @1);
Regards,
Antonio

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Anil, I didn't tried but the link on the first post (The GENERIC Load – QLIKIES) seems a working way to do the table using generic load.
The composite key should be like the TransID field in the 'Clean-up' section, and the key split can be done after the last "for", when the KEY table is already created with all the values.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
True, The clean-up solution should work when simple data set. Even though, Simple Generic won't work for this because when source is Excel or any other DB. That is where i recommend him to do in UI itself. Let's see if Henric offers or not?
