Grouping

[#1]

vinay_bangari‌g.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.

[#2]

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.

[#3]

Hi Rishi,

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

[#4]

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.

[#5]

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

[#6]

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')))));

[#7]

Rishi,

yes that is what i did in the Load script.

[#8]

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

[#9]

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.   

[#10]

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.