topic Grouping in QlikView

Grouping

Tue, 03 Nov 2015 19:53:32 GMT

vinay_bangari‌g.voto76 swuehl‌ phanindra‌

@sunny talwar

Hi Experts,

I'm Trying to do the grouping for Members (i.e., sum(members) in the script. please find the attached qvw file.

1-24 -----> Group 1

25-75 ----> Group2
76-150 ------>Group3

151-299 ------>Group4

>=300 ------>Group5

Thanks for your help in advance.

Re: Grouping

Tue, 03 Nov 2015 20:11:58 GMT

Hope this should solve things:-

Load * , if(sum(members)<=24,'Group 1',

if(sum(members)>24 and sum(members)<=75,'Group 2',

if(sum(members)>75 and sum(members)<=150,'Group 3',

if(sum(members)>150 and sum(members)<=299,'Group 4',

if(sum(members)>299,'Group 5')))));

Please mark the answer accordingly.

Re: Grouping

Tue, 03 Nov 2015 20:20:55 GMT

Hi Rishi,

when i tried to load the above expression in the script it says -- " Invalid expression"

Re: Grouping

Tue, 03 Nov 2015 20:27:44 GMT

You can solve it as rishi stated by read it from resident & read only VEND_ID & above mentioned if clause. Group by VEND_ID which should create those buckets for you in the script.

Thanks

Brad.

Re: Grouping

Tue, 03 Nov 2015 20:32:07 GMT

This is to be used in the load script rather than the expression.

Re: Grouping

Tue, 03 Nov 2015 20:32:56 GMT

If it needs to be applied in expression you can try with the below:

if(sum(members)<=24,'Group 1',

if(sum(members)>24 and sum(members)<=75,'Group 2',

if(sum(members)>75 and sum(members)<=150,'Group 3',

if(sum(members)>150 and sum(members)<=299,'Group 4',

if(sum(members)>299,'Group 5')))));

Re: Grouping

Tue, 03 Nov 2015 20:34:47 GMT

Rishi,

yes that is what i did in the Load script.

Re: Grouping

Tue, 03 Nov 2015 20:39:19 GMT

I think you missed ; at the end. Remove the , after the end braces and put a ;

Re: Grouping

Tue, 03 Nov 2015 20:40:57 GMT

No i would like to create the Buckets in the script.

I know how to write the expression in the front end as you can see that in the GROUPS list box in qvw file.

But i'm just confused how to take that in the Back end Script.

Re: Grouping

Tue, 03 Nov 2015 20:41:52 GMT

Raji,

Try the below script.

Total_memship2016:

LOAD VEND_ID,

mems

FROM

(biff, embedded labels, table is Sheet1$)

WHERE(not IsNull(VEND_ID));

LOAD VEND_ID,

if(sum(mems)<=24,'Group 1',

if(sum(mems)>24 and sum(mems)<=75,'Group 2',

if(sum(mems)>75 and sum(mems)<=150,'Group 3',

if(sum(mems)>150 and sum(mems)<=299,'Group 4',

if(sum(mems)>299,'Group 5'))))) as group

Resident Total_memship2016

Group by VEND_ID;

Thanks

Brad.

Re: Grouping

Tue, 03 Nov 2015 20:45:24 GMT

Thank You... Its working...

Re: Grouping

varunreddy — Tue, 03 Nov 2015 20:47:30 GMT

Try this:

if(sum(members)<=24,'Group 1',

if(sum(members)>24 and sum(members)<=75,'Group 2',

if(sum(members)>75 and sum(members)<=150,'Group 3',

if(sum(members)>150 and sum(members)<=299,'Group 4',

if(sum(members)>299,'Group 5'))))) as xyz;

load

vendor_ID,

Members,

Reportdate

from Table1;

Re: Grouping

ElizaF — Tue, 03 Nov 2015 20:48:33 GMT

Hello,

You can use interval match function. Excellent documentation about function you can find in blog post: IntervalMatch and Slowly Changing Dimensions

Also in attach you can find the solution for your issue with interval match function.

Hope it helps!

Re: Grouping

Wed, 04 Nov 2015 20:45:06 GMT

Hi,

When i checked in the front its not showing the corresponding values. Can you please check this out once. please find the attached screen shot.

Group3 should show the values from 76 to 150.

Re: Grouping

Wed, 04 Nov 2015 20:49:17 GMT

sunindia‌

Hi Sunny,

any help on this please...

Re: Grouping

Wed, 04 Nov 2015 21:06:13 GMT

Raji,

i assume you have multiple values for vend ID & you are checking mems with sum(mems) group.

check the attached qvw. you applied or created groups based on sum(mems) which will show groups for vend Id based on sum(mems) value.

thanks

Brad.

Re: Grouping

Thu, 05 Nov 2015 15:55:04 GMT

Bharadwaaj,

Yes, i have multiple values for vend ID and i would like to show with the total sum(members) value. After summing up the total then it should show the Groups according to the buckets.

In order to get the Total sum(members) what shud i need to do.. please help..

Re: Grouping

Thu, 05 Nov 2015 17:10:58 GMT

your script should be something like this,

dummy:

load [vend id], sum(mems) as TotalMemberSum

FROM

dummy.xlsx

(ooxml, embedded labels, table is Sheet2)

Group by [vend id];

load distinct [vend id],

if((TotalMemberSum)<3,'GRPA',(if((TotalMemberSum)>=4 and (TotalMemberSum)<8,'GRPB',(if((TotalMemberSum)>=9 and (TotalMemberSum) <20,'GRPC','None'))))) as Groups

Resident dummy

Group by [vend id];

you need to do a sum(mems) first and apply buckets on that field & use that field in UI to show vend ID's by total members count & groups.

like in the attached image, for the data set given below.

vend id	mems
Kim	1
john	2
alex	3
farah	4
abe	5
nathan	6
alex	7
abe	8
farah	9
Kim	10

Thanks

Brad.