topic Re: Ovelaping Buckets in QlikView

Ovelaping Buckets

Fri, 13 Sep 2013 20:23:10 GMT

I would like to write a case statement for overlaping puckers

for ex: I have account ids 1,2,3,4,5 with the balance 25,50,75,80,100

Buckets for more than balance of 25. It means everything will fall in this bucket

Buckets for more than 50 ( 2,3,4,5 will fall in this bucket)

Buckets for more than 80

Normal if wont work since we have overlaping buckets. I would like display the data and calculations based on these buckets.

Help will be greatly appriciated.

Thanks,

Abhi

Re: Ovelaping Buckets

Fri, 13 Sep 2013 20:26:28 GMT

You can handle this at script level as front end level

IF(balance<=25,'Bucket 25',IF(balance<=80,'Bucket under 80','Bucket over 80')) as bucket;

Re: Ovelaping Buckets

Fri, 13 Sep 2013 20:30:56 GMT

I have tried this, it wont work, since conflicting data is under 2 buckets, for ex: I have some id's whih has balance of > 25 come in '> 25 and' > 50 buckets, for ex: 80 will should come under both buckets, case statement by if does not work.

Re: Ovelaping Buckets

Carlos_Reyes — Fri, 13 Sep 2013 21:34:39 GMT

You'll have to multiply the number of records by the number of buckets so that if an account falls in N buckets you'll have N records for that account. For instance, I'd do the next:

// Load your Accounts table and join the distinct Bucket values to create all the possible combinations

ACCOUNTS_TEMP:

LOAD

ACCOUNT_ID,

BALANCE

FROM TABLE1;

JOIN

LOAD * INLINE [

BUCKETS

];

// Create a flag in order to know whether an account falls in a bucket and filter unfulfilling records

ACCOUNTS:

LOAD

ACCOUNT_ID,

BALANCE,

' > ' & FALLS_IN_BUCKET AS BUCKET

WHERE NOT ISNULL(FALLS_IN_BUCKET );

LOAD

ACCOUNT_ID,

BALANCE,

IF(BALANCE >= BUCKETS, BUCKETS, NULL()) AS FALLS_IN_BUCKET

RESIDENT ACCOUNTS_TEMP;

DROP TABLE ACCOUNTS_TEMP;