Structure of Table Object in a Qlik Sheet

[tahirhasanjafri]

Hello,

I have some data in the followig format (only an example):

Date, Shift_Nr, Route, Comment, Duration
07.10.2024, 1, VP27, hello, 5
07.10.2024, 2, vp17, hi, 4
07.10.2024, 1, vp76, bye, 1
07.10.2024, 3, vp37, new, 6
07.10.2024, 1, vp17, bike, 7
07.10.2024, 1, vp47, good, 10
07.10.2024, 2, vp27, bad, 85
07.10.2024, 3, vp76, sunny, 0
07.10.2024, 2, vp86, weather, 4

I have loaded this data via the load script from my database. I want to display this data in a table with a different structure. The first two columns shoul be the same i.e. date and shift_nr. Then I want two columns for each distinct route value as comment and duration (for e.g. vp17_comment, vp17_duration, vp27_comment, vp27_duration, etc). Currently if I create a table, the values of cp17+comment and vp27_comment are not displayed side by side for same date and shift nr, instead they are displayed seperately (see the picture attached). How can I achieve the desired result via the set analysis in the sheet itself without changing the load script?

Regards,

Tahir

[VBD]

Hello,

I think you can do something like that with a pivot table and the only() function :

https://help.qlik.com/fr-FR/sense/May2024/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/BasicAggre...

But with the only() function, if you have several values for one date and one shift_nr, it will not works.

Regards,

[tahirhasanjafri]

This can't work because I have several values for each combination of Date, Shift Nr, and Route

[marksouzacosta]

Hi @tahirhasanjafri,

Not sure if I get you right but you can use expressions such as:

MaxString({<Route = {'vp27'}>} Comment)

Sum({<Route = {'vp27'}>} Duration)

Just change the Route value to add the extra columns you need. Please note that vp27 and vp37 will only show up in the same row if both share the same Date and Shift_Nr:

Regards,

Mark Costa

[tahirhasanjafri]

Thanks for your response, but this doesn't solve my query. As I mentioned above in the reply, the combination of Date, Shift, and Route can have multiple values and I want to display them all.

[Kushal_Chawda]

@tahirhasanjafri  if you use table chart to represent your data, you will need to create separate expression for each route which is not ideal. The best way is to create pivot table. You might need to change the representation according to your need but this is what I can think of. Pivot table is the only option to represent your data dynamically unless you prefer to create separate expression for each route.

I have used below dataset

Date, Shift_Nr, Route, Comment, Duration
07.10.2024, 1, VP27, hello, 5
07.10.2024, 1, VP27, bye, 6
07.10.2024, 1, VP27, hi, 2
07.10.2024, 1, vp17, hello, 4
07.10.2024, 1, vp17, bye, 1
07.10.2024, 1, vp17, hi, 6
07.10.2024, 2, vp17, hi, 4
07.10.2024, 2, vp17, bye, 1
07.10.2024, 2, vp17, new, 6
07.10.2024, 3, vp47, bike, 7
07.10.2024, 3, vp47, good, 10
07.10.2024, 3, vp47, bad, 85

Below is what you can produce with pivot table

[tahirhasanjafri]

Hi, thanks for your reply and solution. I don't mind creating seperate expressions for every route as long as I can get the values for each combination of date and shift number side by side for every route. And that's where I am struggling. Please see the attached picture for reference. This is what I am getting. Even though I have values for comment and duration for 2 routes on the same day and shift number, they are not aligning in the same row. I just want that to happen.