Transpose a table ?

[#1]

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

[alexandros17]

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]

generic

LOAD [ID         ],

     [     Status       ],

     [         timestamp]

FROM

Book2.xlsx

(ooxml, embedded labels, table is Sheet1);

[#4]

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!

[daveamz]

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]

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