topic Re: Calculated grouping in QlikView

Calculated grouping

viveksingh — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I have year with some measures, and I want to categorize the measures as a header.

input:

Year	Measure_1	Measure_2	Measure_3	Measure_4	Measure_5
2010	100	0	50	110	70
2011	200	100	112	90	23
2012	0	115	14	0	323
2014	30	250	60	0	0

expecting output:

please help in achieving this.

Re: Calculated grouping

YoussefBelloum — Mon, 23 Jul 2018 14:31:42 GMT

Hi,

table:

load

Year,

Measure1,

Measure2,

'IT GROUP' as Group

FROM...

join(table) //Or just Join

load

Year,

Measure3,

Measure4,

Measure5,

'HR GROUP' as Group

FROM...

Re: Calculated grouping

sunny_talwar — Mon, 23 Jul 2018 14:32:41 GMT

Like this?

Re: Calculated grouping

viveksingh — Tue, 24 Jul 2018 01:08:54 GMT

‌yes , sunn. This is what I’m lookin. I got the result. But ehen I export the data to exce, I’m getting group names on top of each measure. Like below

	Grouping	HR Group	HR Group	HR Group	IT Group	IT Group
Year	Measure	Measure_3	Measure_4	Measure_5	Measure_1	Measure_2
2010
2011
2012
2015

I Want to merge this group.

Re: Calculated grouping

qlikviewwizard — Tue, 24 Jul 2018 03:00:21 GMT

You can achieve your requirement like this.

Please check this.

Data_Temp:

LOAD * INLINE [

Year,Measure_1,Measure_2,Measure_3,Measure_4,Measure_5

2010,100,0,50,110,70

2011,200,100,112,90,23

2012,0,115,14,0,323

2014,30,250,60,0,0

];

Data:

Crosstable (Measure, Sales, 1) LOAD * RESIDENT Data_Temp;

Left join

LOAD *,if(WildMatch(Measure,'Measure_1','Measure_2'),'IT Group',

if(WildMatch(Measure,'Measure_3','Measure_4','Measure_5'),'HR Group')) as Group Resident Data;

drop table Data_Temp;

Re: Calculated grouping

viveksingh — Tue, 24 Jul 2018 03:21:41 GMT

‌tthanks for the reply. I can achieve this with sample data. But I have 120 measures under one group.

appreciate if any one provide solution

Re: Calculated grouping

qlikviewwizard — Tue, 24 Jul 2018 03:28:48 GMT

No Problem.

For example below data, we have one dimension. That is Year. so that we have used Crosstable (Measure, Sales, 1) LOAD * RESIDENT Data_Temp;

If you have Year,Dim1,dim2,Dim3 , use Crosstable (Measure, Sales, 4) LOAD * RESIDENT Data_Temp;

Hope you got it.

Data_Temp:

LOAD * INLINE [

Year,Measure_1,Measure_2,Measure_3,Measure_4,Measure_5

2010,100,0,50,110,70

2011,200,100,112,90,23

2012,0,115,14,0,323

2014,30,250,60,0,0

];