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.
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.
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.
Hi Rishi,
when i tried to load the above expression in the script it says -- " Invalid expression"
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.
This is to be used in the load script rather than the expression.
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')))));
Rishi,
yes that is what i did in the Load script.
I think you missed ; at the end. Remove the , after the end braces and put a ;
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.
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.