topic Transpose a table ? in QlikView

Transpose a table ?

Wed, 04 Feb 2015 12:56:07 GMT

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

Re: Transpose a table ?

alexandros17 — Wed, 04 Feb 2015 13:02:24 GMT

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

Re: Transpose a table ?

robert_mika — Wed, 04 Feb 2015 13:10:55 GMT

generic

LOAD [ID ],

[ Status ],

[ timestamp]

FROM

Book2.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: Transpose a table ?

Wed, 04 Feb 2015 18:41:00 GMT

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,

Tiempo as

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!

Re: Transpose a table ?

daveamz — Fri, 06 Feb 2015 15:49:22 GMT

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

Re: Transpose a table ?

Anonymous — Fri, 06 Feb 2015 16:40:12 GMT

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