QV Union from one table

[Anonymous]

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.

[sunny_talwar]

I think what you need here is The Crosstable Load

[tracysmart]

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;

[Anonymous]

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.