Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I have a table AAA like this :
ID Status timestamp
XX1 A 10/10/2015 04:00:15
XX1 B 11/10/2015 04:20:15
XX1 C 11/10/2015 22:00:15
XX2 A 14/11/2015 23:00:12
XX2 C 15/11/2015 23:07:18
And I would like a table BBB like this :
ID A B C
XX1 10/10/2015 04:00:15 11/10/2015 04:20:15 11/10/2015 22:00:15
XX2 14/11/2015 23:00:12 - 15/11/2015 23:07:18
How can I do it ? I have heard about the crosstable load function but I think it is more to do it on the other way ? (from the AAA table to the BBB table)
Any ideas ?
Thank you
Have a good day
Laura
There is a check box in presentation tab , is "Horizontal", try to check in and verify if the result is what you expect.
let me know
generic
LOAD [ID ],
[ Status ],
[ timestamp]
FROM
Book2.xlsx
(ooxml, embedded labels, table is Sheet1);
Hi Laura.
If you need the table BBB; can try the following:
Test:
LOAD * INLINE [
ID, Status, tiempo
XX1, A, 10/10/2015 04:00:15
XX1, B, 11/10/2015 04:20:15
XX1, C, 11/10/2015 22:00:15
XX2, A, 14/11/2015 23:00:12
XX2, C, 15/11/2015 23:07:18
];
Test2:
LOAD
ID,
Status,
date#(tiempo) as Tiempo
Resident Test;
DROP Table Test;
Test3:
LOAD
ID,
Resident Test2 Where Status = 'A';
Join
LOAD
ID,
Tiempo as
Resident Test2 Where Status='B';
Join
LOAD
ID,
Tiempo as
Resident Test2 Where Status='C';
Or if you need a chart whit this transpose you maybe try the following:
Dimensions: ID, Status
Expressions: Tiempo
Have a good day!
Hi Laura,
OriginalTable:
LOAD ID,
Status,
timestamp
FROM ....;
Data:
LOAD DISTINCT ID
RESIDENT OriginalTable;
FOR Each vStatus IN 'A', 'B', 'C'
LEFT JOIN (Data)
LOAD ID,
timestamp as [$(vStatus)]
RESIDENT OriginalTable WHERE Status = '$(vStatus)';
NEXT vStatus;
DROP TABLE OriginalTable;
Regards,
David
Hi,
Generic load is required to get desired output,it transposes rows into columns.
Opposite to cross table.
See below links for generic load:
http://community.qlik.com/docs/DOC-3020
http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic
Regards
Neetha