topic Re: Creating Buckets in the Load Script in QlikView

Creating Buckets in the Load Script

Wed, 13 Mar 2013 12:31:16 GMT

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.

Re: Creating Buckets in the Load Script

Gysbert_Wassenaar — Wed, 13 Mar 2013 12:35:25 GMT

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

Re: Creating Buckets in the Load Script

VishalWaghole — Wed, 13 Mar 2013 12:52:59 GMT

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

Re: Creating Buckets in the Load Script

Wed, 13 Mar 2013 14:31:48 GMT

Hey

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

But still i am unable to load..

An error comes up.

Re: Creating Buckets in the Load Script

Gysbert_Wassenaar — Wed, 13 Mar 2013 14:34:19 GMT

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;

Re: Creating Buckets in the Load Script

Wed, 13 Mar 2013 15:02:15 GMT

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