
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Crosstable Transpose Rows into Columns Qlik sense
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?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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()
));
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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()
));

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Amazing man!
it worked, how did you do that could you explain to me?
Thanks you
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
