Creating Buckets in the Load Script

[#1]

My Objective is to plot the Lag time on a chart; expressing the frequency of the data in buckets - which would represent the number of months between the Claim Date and a Purchase Date.

I have already made a pre-calculation in the SQL that provide the number of days between Claim and the Purchase. This is now called LTDays,

My next step in the script was to convert the LTDays intoa month figure - which would look like: (LTDays/365*12)

This is my intended bucket script - but it doesn't seem to work.

//Script for LTMonths

LOAD

     (LTDays/365*12) as Lt Months

Resident SynergyCases;

CASE  if (LTMonths) =>0 AND <=1 THEN "0-1"

  if (LTMonths) =>2 AND <=3 THEN "2-3"

  if (LTMonths) =>4 AND <=5 THEN "4-5"

  if (LTMonths) =>6 AND <=7 THEN "6-7"

  if (LTMonths) =>8 AND <=9 THEN "8-9"

  if (LTMonths) =>10 AND <=11 THEN "10-11"

  if (LTMonths) =>12 AND <=13 THEN "12-13"

  if (LTMonths) =>14 AND <=15 THEN "14-15"

  if (LTMonths) =>16 AND <=17 THEN "16-17"

  if (LTMonths) =>18 AND <=19 THEN "18-19"

  if (LTMonths) =>20 AND <=21 THEN "20-21"

  if (LTMonths) =>22 AND <=23 THEN "22-23"

  if (LTMonths) =>24 THEN "24+"

  ELSE "Unknown"

  END as BUCKET,

Could you please provide some information.

[Gysbert_Wassenaar]

try if(LTDays/365>=24,'24+',class(LTDays/365,1)) as BUCKET

[VishalWaghole]

Hi,

Do not use this expression in Case.

First Calculate LTMonth. like.........

Test:

LOAD *,

            (LTDays/365*12) as Lt Months;

Resident SynergyCases;

Drop table SynergyCases;

Test1:

Load *,

           if ((LTMonths) >=0 and (LTMonths) <=1, '0-1',
          if((LTMonths) >=1 and (LTMonths) <=2, '1-2 yrs',
          if((LTMonths) >=2 and (LTMonths) <=3, '2-3 yrs',
          if((LTMonths) >=3 and (LTMonths) <=4, '3-4 yrs',
          if((LTMonths) >=4 and (LTMonths) <=5, '4-5 yrs',
          if((LTMonths) >=5 and (LTMonths) <=6, '5-6 yrs',
          if((LTMonths) >=6 , '6 and Above Month',))))))) AS BucketMonth;

  Resident Test1;

Drop table Test1;

This will ceate BucketMonth as your bucket field.

Thanks and Regards,

VIshal

[#4]

Hey

Thanks for your imput. The script was altered to look like this.

But still i am unable to load..

An error comes up.

[Gysbert_Wassenaar · Accepted solution]

You name your table Test1 and then want to get the data for that table from Test1. That won't work. Give your first table where you create LtMonths a name and use that name with the resident keyword in the second load.

Or just do it with one load:

Test:

LOAD *,

if(LTDays/365>=24,'24+',class(LTDays/365,1)) as BUCKET

Resident SynergyCases;

[#6]

Thanks for that assistance.

This solved my errors and met my objectives

However (as i'm using the personal edition), the internal memory ran out...

But this is another topic

Thanks again