Calculated grouping

[viveksingh]

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:

hi

please help in achieving this.

[YoussefBelloum]

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...

[sunny_talwar]

Like this?

[viveksingh]

‌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.

[qlikviewwizard]

Hi

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;

[viveksingh]

‌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

[qlikviewwizard · Accepted solution]

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

];