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
From the image, looks like it works as designed. What's the expectations?
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.
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
Hi,
another solution for unequal sized buckets:
maybe it helps.
regards
Marco
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...
Yes, you are right. I didn't notice (maybe it's too late in the night over here ... )
regards
Marco
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)
;
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.
can you post a screenshot of your data model? My guess is the problem is there.