Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi all,
i want to make groups by an if sentence but i get a lot of nulls values
LOAD Distinct
ID as ACCOUNT_ID,
ACCOUNT_LT,
if(ACCOUNT_LT>= 0 and ACCOUNT_LT<= 7 ,'0-7 Days',
if(ACCOUNT_LT> 7 and ACCOUNT_LT<= 14,'8-14 Days',
if(ACCOUNT_LT> 14 and ACCOUNT_LT<= 21,'15-21 Days',
if(ACCOUNT_LT> 21 and ACCOUNT_LT<= 28,'22-28 Days',
if(ACCOUNT_LT> 28 and ACCOUNT_LT<= 36,'29-36 Days',
if(ACCOUNT_LT> 36 and ACCOUNT_LT<= 43,'37-43 Days',
if(ACCOUNT_LT > 43 and ACCOUNT_LT <= 50,'44-50 Days',
if(ACCOUNT_LT >50 and ACCOUNT_LT <= 57 ,'50-57 Days',
if(ACCOUNT_LT> 57 and ACCOUNT_LT<= 63,'57-63 Days','64+'))))))))) as ACCOUNT_Group
the result is that most of the values are out of range
when i select suppress null CB in chart last bar in chart disappear
and count does not mach.
;
Ilan,
I see what you mean. The way it is written, everything out of range (negative values, non-numeric, and > 63) should be in '64+'. Probably not what you need - but it doesn't work even this way. I think tko has the point. It would be easier to figure out if you could upload the app.
Regards,
Michael
Hi
excuse me but because your gap between to values is always 7 why don't you use CLASS function like :
if (ACCOUNT_LT > 63, '64+', // to meet the values over 63
class(ACCOUNT_LT, 7) // to range by group of 7
) as ACCOUNT_GROUP
Chris
Refer the attchment for solution.
Hope it will help you.
Regards,
Sindhu Kannan.