topic Re: QV Union from one table in QlikView

QV Union from one table

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a table that I need to reorganise a bit in the Load Script to make rows out of data in separate columns.

For instance:

Date	Code1	Time1	Reason1	Code2	Time2	Reason2	Code3	Time3	Reason3
1/11/15	ABC	34	Test1	DEF	23	Test2
2/6/15	DEF	5	Test3
31/7/15	GHI	12	Test4	JKL	3	Test5	ABC	30	Test6
8/2/16	MNO	9	Test7
15/8/16	DEF	3	Test8	ABC	106	Test9

And what I need is:

Date	Code	Time	Reason
1/11/15	ABC	34	Test1
1/11/15	DEF	23	Test2
2/6/15	DEF	5	Test3
31/7/15	GHI	12	Test4
31/7/15	JKL	3	Test5
31/7/15	ABC	30	Test6
8/2/16	MNO	9	Test7
15/8/16	DEF	3	Test8
15/8/16	ABC	106	Test9

The entire table is already loaded (with lots of other information) but I need to be able to do some analysis on the codes individually.

This should be really simple, but I just can't see it.

Re: QV Union from one table

sunny_talwar — Thu, 18 Aug 2016 10:44:33 GMT

I think what you need here is The Crosstable Load

Re: QV Union from one table

tracysmart — Thu, 18 Aug 2016 10:54:55 GMT

You might want to look at the Crosstable load and pivot these columns around in the script.

Other options would be to rebuild the table using resident loads and create 3 columns for Code, Time and reason . The trouble here is you will also be repeating all the other columns in the table 3 times and this could give you incorrect results as granularity of data has changed.

You could build a table to the side of the main table linking on just date and have 3 new columns Code, Time and reason in here. This wont affect the granularity of the data but you are adding an extra join and extra data.

NewTable:
LOAD
DATE,
CODE1 AS Code,
Time1 AS Time,
Reason1 AS Reason
Resident MyTable;
concatenate
LOAD
DATE,
CODE2 AS Code,
Time2 AS Time,
Reason2 AS Reason
Resident MyTable;
concatenate
LOAD
DATE,
CODE3 AS Code,
Time3 AS Time,
Reason3 AS Reason
Resident MyTable;

Re: QV Union from one table

Anonymous — Thu, 18 Aug 2016 12:31:41 GMT

Think I've got it. What I ended up doing is 3 Unions on the same table to load the date, code, mins, and reason.

CrossTable looked promising, but didn't seem to handle the four output columns unless I'm missing something.