topic Crosstable multiple columns into rows in 3 columns Qlik Sense in App Development

Crosstable multiple columns into rows in 3 columns Qlik Sense

h2bi — Fri, 19 Jan 2024 07:22:48 GMT

So i have a table that containes Dates, Person, and Task name next to it the time dedicated to that task:

mytable:
LOAD
Fecha as Dates,
Person,
Cleanning,
Time,
DIY,
Time1,
Paperwork,
Time2,
Flushing,
Time3
Resident res;

the output:

Each task column is followed by a time column for that task

Cleanning column contains number of tasks done in that type and Time column conatins the time spent on each task number

I want to achieve a table that has Date, Person, Task Type, Number of tasks, Time . Which merges all task types in 1 column Task Type and all times into Time column

I tried with a crosstable but it is merging Time, Time1, Time2, Time3... with the task types

Crosstable result:

cross_res:

CrossTable(Task_type, values, 2)

Load

Resident mytable;

drop Table mytable;

The output of crosstable:

Re: Crosstable multiple columns into rows in 3 columns Qlik Sense

rwunderlich — Thu, 18 Jan 2024 20:21:32 GMT

I think you will need to do separate Loads for each task type, specifying just one task and time field on each.

CrossTable(Task_Type, Time, 2)
LOAD Person, Dates, Cleaning, Time
Resident mytable;

CrossTable(Task_Type, Time, 2)
LOAD Person, Dates, DIY, Time1
Resident mytable;

etc

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Re: Crosstable multiple columns into rows in 3 columns Qlik Sense

h2bi — Fri, 19 Jan 2024 07:22:21 GMT

Hello, the output will be this:

But the needed output is Person, Dates, Task_Type, Number of tasks, Time

It is merging Time and the task into 1 column, the output needed must look like this:

Person Dates Task Number of tasks Time

Jack 45362 Cleanning 34 454

Jack 45362 Cleanning 456 345

Michael 45962 Cleanning 65 456

Any ideas?

Re: Crosstable multiple columns into rows in 3 columns Qlik Sense

rwunderlich — Fri, 19 Jan 2024 16:33:52 GMT

I didn't read your first post carefully enough. I believe your solution is not crosstable but:

LOAD Person, Dates,
'Cleaning' as Task , Cleaning as [Number of Tasks], Time
Resident mytable;

LOAD Person, Dates,
'DIY' as Task , DIY as [Number of Tasks], Time1 as Time
Resident mytable;

etc...

-Rob