Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

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

View solution in original post

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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
Highlighted
Specialist II
Specialist 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

Highlighted
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

Highlighted
MVP & Luminary
MVP & Luminary

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

View solution in original post

Highlighted
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