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]

From the image, looks like it works as designed.  What's the expectations?

[ToniKautto]

The screenshot refers to a field called ID, but this field does not exist in your sample script, so there is a gap in detail there. The null value typically comes from the way your aggregation spans over the data model, so a sample QVW would make it easier to explain where the null values come from.

[MarcoWedel]

Hi,

are there any ACCOUNT_LT < 0 or non-numeric, because those would not be put in one of the ACCOUNT_Groups using your expression?

regards

Marco

[MarcoWedel]

Hi,

another solution for unequal sized buckets:

Custom Filter(unusual range)

maybe it helps.

regards

Marco

[Anonymous]

Marco,

That was my first thought too, but given how it's written, everything out of range should be in '64+' group.

Hard to tell without an example...

[MarcoWedel]

Yes, you are right. I didn't notice (maybe it's too late in the night over here ... )

regards

Marco

[nagaiank]

Please try the following and see if the problem resolves:.

LOAD *,

  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

  ;

LOAD Distinct

ID as ACCOUNT_ID

ACCOUNT_LT

From  (your source of data)

;

[ilanbaruch]

Hi Michael,

in my script I created groups with limits of 7 days, last condition should contain all the values grater then 64 days

in the attached screen shot you can see that that the group of the 64+ contains 206 and there is another group (not defined in the script) represented by '-' and contains 13,721 and is not selectable.

also you can see in caption total count 20,047 and when I select suppress null, the total count of account in groups does not mach.

[giakoum]

can you post a screenshot of your data model? My guess is the problem is there.