Ovelaping Buckets

[#1]

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

[#2]

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;

[#3]

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.

[Carlos_Reyes]

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

25

50

80

];

// 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;