Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Transpose a table ?

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

5 Replies
alexandros17
Partner - Champion III
Partner - Champion III

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

robert_mika
Master III
Master III

generic

LOAD [ID         ],

     [     Status       ],

     [         timestamp]

FROM

Book2.xlsx

(ooxml, embedded labels, table is Sheet1);

Not applicable
Author

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:

Transpose.png

Dimensions: ID, Status

Expressions: Tiempo

Have a good day!

daveamz
Partner - Creator III
Partner - Creator III

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

Anonymous
Not applicable
Author

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