topic Re: Generic load with two dimensions? in QlikView

Generic load with two dimensions?

Sat, 08 Jul 2017 19:48:46 GMT

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‌

Re: Generic load with two dimensions?

rubenmarin — Sun, 09 Jul 2017 16:47:00 GMT

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,

...

Re: Generic load with two dimensions?

Anil_Babu_Samineni — Sun, 09 Jul 2017 16:53:20 GMT

I wonder, Why this need script i meant to say while load. One front end solution may be this?

Re: Generic load with two dimensions?

Anil_Babu_Samineni — Sun, 09 Jul 2017 17:03:00 GMT

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 ...;

Re: Generic load with two dimensions?

antoniotiman — Sun, 09 Jul 2017 17:03:23 GMT

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

Re: Generic load with two dimensions?

rubenmarin — Sun, 09 Jul 2017 17:41:45 GMT

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.

Re: Generic load with two dimensions?

Anil_Babu_Samineni — Sun, 09 Jul 2017 17:51:35 GMT

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?