topic Re: groups by if sentence in QlikView

groups by if sentence

ilanbaruch — Wed, 03 Jun 2015 22:46:28 GMT

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.

;

Re: groups by if sentence

Anonymous — Wed, 03 Jun 2015 23:06:17 GMT

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

Re: groups by if sentence

ToniKautto — Wed, 03 Jun 2015 23:20:50 GMT

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.

Re: groups by if sentence

MarcoWedel — Wed, 03 Jun 2015 23:24:48 GMT

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

Re: groups by if sentence

MarcoWedel — Wed, 03 Jun 2015 23:28:08 GMT

Hi,

another solution for unequal sized buckets:

Custom Filter(unusual range)

maybe it helps.

regards

Marco

Re: groups by if sentence

Anonymous — Wed, 03 Jun 2015 23:29:56 GMT

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...

Re: groups by if sentence

MarcoWedel — Wed, 03 Jun 2015 23:39:58 GMT

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

regards

Marco

Re: groups by if sentence

nagaiank — Thu, 04 Jun 2015 00:53:53 GMT

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)

;

Re: groups by if sentence

ilanbaruch — Thu, 04 Jun 2015 06:31:33 GMT

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.

Re: groups by if sentence

giakoum — Thu, 04 Jun 2015 06:50:09 GMT

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

Re: groups by if sentence

Anonymous — Thu, 04 Jun 2015 12:37:30 GMT

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

Re: groups by if sentence

Thu, 04 Jun 2015 12:47:07 GMT

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

Re: groups by if sentence

Thu, 04 Jun 2015 13:17:05 GMT

Refer the attchment for solution.

Hope it will help you.

Regards,

Sindhu Kannan.