Crosstable Transpose Rows into Columns Qlik sense

[h2bi]

So i have a table in an excel sheet, which people enter data manualy, each person registers the number of task done and time for each task under his name and that day's date

For example i am Jack i did 23 cleanning tasks that took me 24 minutes in 8/10/2023, Jack registered 23 and 24 in Cleanning and Time rows in 08/10/2023 and Jack column as you can see

Qlik loads the table like this:
table:
LOAD
   "Date",
    "45207",
    "452071",
    "452072",
    "45210",
    "45211",
    "45216",
    "45223"
FROM [lib://table.xlsx]
(ooxml, embedded labels, table is Sheet1);

Which those numbers are the dates in numeric values, the table looks like this when loaded:

So i need to transpose the rows into columns to look like this in Qlik sense script editor:

I tried croosstable but it shows like this:

The script

The output

Note: i cannot edit the excel sheet, everything must be done in QLik sense

Any idea of a possible solution?

[Anil_Babu_Samineni · Accepted solution]

@h2bi Perhaps this

LOAD Date, Person, Cleanning, Time, DIY, Time1, Paperwork, Time2, Flushing, Time3
FROM [lib://AttachedFiles/table.xlsx]
(ooxml, embedded labels, table is Sheet1, filters(
Rotate(left),
Rotate(left),
Rotate(left),
Rotate(left),
Transpose()
));

[h2bi]

Amazing man!

it worked, how did you do that could you explain to me?

Thanks you

[Anil_Babu_Samineni]

@h2bi It's kind of transpose() for bad data structure. I used Qlik view for this (It has more flexible) and share it with you. 

[h2bi]

Thanks a lot man! i really appreciate it, now that i have each task name in a column and next to it its Time column, do you have any idea on how can i put all those task names into 1 column and next to it the corresponding number of task new column and a Time column like this

Date, Person, Task, NumberofTasks, Time

[Or]

For dealing with Excel files, I strongly recommend grabbing a copy of QlikView. The Excel wizard there makes life so much easier for dealing with messy structures. While Qlik Sense unfortunately lacks matching functionality, it does use the same syntax, so the code can be copy-pasted over.