Transpose Table

Wed, 22 Jul 2015 20:17:30 GMT

I need to transpose my data, and have no clue how to do it.

In my attached spreadsheet (First sheet) is the raw data. I want to extract data and transpose like in Sheet 2.

Can someone help please ?

Re: Transpose Table

cspencer3 — Wed, 22 Jul 2015 20:29:50 GMT

Is this something you could use the 'Crosstable' function for when loading your excel file? Using the table files Wizard there is an option for CrossTables, which is similar to transposing.

Re: Transpose Table

MarcoWedel — Wed, 22 Jul 2015 22:01:36 GMT

Hi,

one solution could be:

table1:
CrossTable (Col,Duration)
LOAD @3 as Task,
    @7,
    @8,
    @9,
    @10,
    @11,
    @12,
    @13,
    @14,
    @15,
    @16,
    @17,
    @18,
    @19,
    @20,
    @21,
    @22,
    @23,
    @24,
    @25,
    @26,
    @27,
    @28,
    @29,
    @30,
    @31,
    @32,
    @33,
    @34,
    @35,
    @36,
    @37
FROM [https://community.qlik.com/servlet/JiveServlet/download/828329-177241/TimeSheet.xlsx] (ooxml, no labels, table is [Employee Timesheet], filters(Remove(Row, Pos(Top, 8)),Remove(Row, Pos(Top, 7)),Remove(Row, Pos(Top, 6)),Remove(Row, Pos(Top, 5)),Remove(Row, Pos(Top, 4)),Remove(Row, Pos(Top, 3)),Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 1))));

tabDates:
CrossTable (Col, DateNum)
LOAD 1,
    @7,
    @8,
    @9,
    @10,
    @11,
    @12,
    @13,
    @14,
    @15,
    @16,
    @17,
    @18,
    @19,
    @20,
    @21,
    @22,
    @23,
    @24,
    @25,
    @26,
    @27,
    @28,
    @29,
    @30,
    @31,
    @32,
    @33,
    @34,
    @35,
    @36,
    @37
FROM [https://community.qlik.com/servlet/JiveServlet/download/828329-177241/TimeSheet.xlsx] (ooxml, no labels, table is [Employee Timesheet], filters(Remove(Row, Pos(Top, 6)),Remove(Row, Pos(Top, 5)),Remove(Row, Pos(Top, 4)),Remove(Row, Pos(Top, 3)),Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 1))))
Where RecNo()=1;

Left Join (table1)
LOAD Col,
    Date(DateNum) as Date
Resident tabDates;

DROP Tables tabDates;
DROP Field Col;

Right Join (table1)
LOAD Distinct Duration
Resident table1
Where IsNum(Duration);

Right Join (table1)
LOAD Distinct Task
Resident table1
Where Len(Task);

hope this helps

regards

Marco

Re: Transpose Table

Thu, 23 Jul 2015 07:07:56 GMT

Marco, this is awesome !! And BRILLIANT !! Never woulve thought about doing it that way. You're a genius !!!

Thanks !!!

Re: Transpose Table

Thu, 23 Jul 2015 19:42:45 GMT

Hi Mark. Any idea how i can add the name of the person (Cell D3) to the table ?

topic Transpose Table in QlikView

Transpose Table

Re: Transpose Table

Re: Transpose Table

Re: Transpose Table

Re: Transpose Table