topic Re: Grouping Columns in Pivot Table - Qlik Sense in App Development

Grouping Columns in Pivot Table - Qlik Sense

rougeherring — Sat, 16 Nov 2024 01:48:54 GMT

In Qlik Sense using a pivot table, how can I add a row header for measures. For example, Tires, Tire Fix need to be grouped as Wheel Shop. Engine Work, Oil, Transmission need to be grouped as Engine Shop. Dent, Scratch as auto body. How can I group them in the Pivot table? The data loaded into Qlik Sense looks exactly as shown below.

	Tires	Tire Fix	Engine Work	Oil Change	Transmission	Dent	Scratch
Max Auto	4	4	0	6	7	8	7
Jim's Auto	5	6	0	20	8	0	4
Tim's Engine Works	4	6	4	55	2	1	0
Sam's Full Service	0	5	5	36	12	2	0

I have seen recommendations to create an inline table with the field names the placement is not working so I'm at a loss.

Re: Grouping Columns in Pivot Table - Qlik Sense

Lisa_P — Fri, 24 Jul 2020 00:31:15 GMT

I created another table as inline table as per below:

with resulting pivot table

Re: Grouping Columns in Pivot Table - Qlik Sense

rougeherring — Fri, 24 Jul 2020 01:12:12 GMT

With the addition of the inline table, how did you make the service data relate to the name of a field? This is exactly what I need but I'm missing the step between the images.

Re: Grouping Columns in Pivot Table - Qlik Sense

Lisa_P — Fri, 24 Jul 2020 01:17:25 GMT

Can you show a screenshot of your data model viewer to see the name of your fields

Re: Grouping Columns in Pivot Table - Qlik Sense

rougeherring — Fri, 24 Jul 2020 01:46:02 GMT

AutoData:
Load * Inline [
Autoshop, Tires, Tire Fixes, Engine Work, Oil Changes, Transmissions, Dent Fixes, Scratches
Max's Auto,4,4,0,6,7,8,7
Jim's Auto,5,6,0,20,8,0,4
Tim's Engine Works,4,6,4,55,2,1,0
Sam's Full Service,0,5,5,36,12,2,0
];

The dataset is exactly like this with 100,000 unique locations, each with a single record.

Re: Grouping Columns in Pivot Table - Qlik Sense

Lisa_P — Fri, 24 Jul 2020 01:51:33 GMT

Use a crosstable to unpivot the data as below:

AutoData:
CROSSTABLE ([Service],[Amount],1)
LOAD * INLINE
[
Autoshop,Tires,Tire Fix,Engine Work,Oil Change,Transmission,Dent,Scratch
Max Auto,4,4,0,6,7,8,7
Jim's Auto,5,6,0,20,8,0,4
Tim's Engine Works,4,6,4,55,2,1,0
Sam's Full Service,0,5,5,36,12,2,0
](delimiter is ',');

[Groups]:
LOAD * INLINE
[
Service,Group
Tires,Wheel Shop
Tire Fix,Wheel Shop
Engine Work,Engine Shop
Oil Change,Engine Shop
Transmission,Engine Shop
Dent,Auto Body
Scratch,Auto Body
](delimiter is ',');

Re: Grouping Columns in Pivot Table - Qlik Sense

Mahamed_Qlik — Mon, 16 Aug 2021 09:46:44 GMT

what if ,4,4,0,6,7,8,7 are calculated.
Here you have directly pass hardcode values...what if they are calcualting...?

Re: Grouping Columns in Pivot Table - Qlik Sense

sharmi1990 — Fri, 13 Jan 2023 01:09:01 GMT

@Lisa_P Any updates to the question above?What if the values are calculated and not hard coded?

Re: Grouping Columns in Pivot Table - Qlik Sense

markuskoessler — Thu, 09 Nov 2023 10:35:04 GMT

you could do something like this, within you meassure:

if(Service='Dent',1+1,
if(Service='Scratch',1+2,
if(Service='Engine Work',1+3,
if(Service='Oil Change',1+4,
if(Service='Transmission',1+5,
if(Service='Tire Fix',1+6,
if(Service='Tires',1+7
)))))))