Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Not applicable

Re: Transpose a table ?

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
Not applicable

Re: Transpose a table ?

generic

LOAD [ID         ],

     [     Status       ],

     [         timestamp]

FROM

Book2.xlsx

(ooxml, embedded labels, table is Sheet1);

Not applicable

Re: Transpose a table ?

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!

daveamz01
Not applicable

Re: Transpose a table ?

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

neetha_p
Not applicable

Re: Transpose a table ?

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