Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Creating Buckets in the Load Script

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.

1 Solution

Accepted Solutions

Re: Creating Buckets in the Load Script

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;


talk is cheap, supply exceeds demand
5 Replies

Re: Creating Buckets in the Load Script

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


talk is cheap, supply exceeds demand
vishalwaghole
Valued Contributor II

Re: Creating Buckets in the Load Script

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

Not applicable

Re: Creating Buckets in the Load Script

Hey

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

But still i am unable to load..

An error comes up.

script_now.PNG

Re: Creating Buckets in the Load Script

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;


talk is cheap, supply exceeds demand
Not applicable

Re: Creating Buckets in the Load Script

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

Community Browser