Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Partner
Partner

Grouping/Aggregating based on variance %

Hi all

I have a table with 3 columns like below (CRM, Brand as measures and Var as%)


Sample.PNG

 

I want to group this into a table which counts the unique CRM ID's and groups them by Brand based on their variance

Below is a sample of that (it does not have the exact figures in there, these are just a guide)

Sample2.PNG

 

How do we do this with a QlikView load scipt?

 

I've attached the XLS data as an example.

 

 

1 Solution

Accepted Solutions
zzyjordan
Contributor II

Re: Grouping/Aggregating based on variance %

Hi, there 

Try this

[Sheet1]:
LOAD [CRM ID],
[Brand],
[Bgt Var %],
if([Bgt Var %] <-0.2, '<-20%',if([Bgt Var %]>=-0.2 and [Bgt Var %]< -0.1,'-20% to -10%','-10% to 0')) as "Bgt Var% Range"
FROM [lib://AttachedFiles/Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

 

And then in pivot table, drag the new column and brand as dimension, count(distinct [CRM ID])  as measure.

 Capture.PNG

Hope this helps

 

ZZ

3 Replies
zzyjordan
Contributor II

Re: Grouping/Aggregating based on variance %

Hi, there 

Try this

[Sheet1]:
LOAD [CRM ID],
[Brand],
[Bgt Var %],
if([Bgt Var %] <-0.2, '<-20%',if([Bgt Var %]>=-0.2 and [Bgt Var %]< -0.1,'-20% to -10%','-10% to 0')) as "Bgt Var% Range"
FROM [lib://AttachedFiles/Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

 

And then in pivot table, drag the new column and brand as dimension, count(distinct [CRM ID])  as measure.

 Capture.PNG

Hope this helps

 

ZZ

Partner
Partner

Re: Grouping/Aggregating based on variance %

Thanks very much! that works fine

zzyjordan
Contributor II

Re: Grouping/Aggregating based on variance %

You are welcome 🙂
ZZ