groups by if sentence

[ilanbaruch]

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.

;

[Anonymous · Accepted solution]

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

[#3]

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

[#4]

Refer the attchment for solution.

Hope it will help you.

Regards,

Sindhu Kannan.