Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Grouping

vinay_bangarig.voto76swuehlphanindra

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

17 Replies
Not applicable
Author

Thank You... Its working...

varunreddy
Creator III
Creator III

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;

ElizaF
Creator II
Creator II

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!

Not applicable
Author

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.

group3.png

Not applicable
Author

sunindia

Hi Sunny,

any help on this please...

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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 idmems
Kim1
john2
alex3
farah4
abe5
nathan6
alex7
abe8
farah9
Kim10

Thanks

Brad.