Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
vinay_bangarig.voto76swuehl 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.
Thank You... Its working...
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;
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!
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.
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.
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..
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.