Transpose Table

[#1]

Hi

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 ?

[cspencer3]

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.

[MarcoWedel · Accepted solution]

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

[#4]

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

Thanks !!!

[#5]

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