topic Re: Lighten up the script in QlikView

Lighten up the script

Tue, 25 Feb 2014 10:54:04 GMT

Hi everyone,

I'm resquesting your help for one little thing. I'm trying to lighten a part of my script.

The idea is : if you don't pay for the bill until a certain time, you've got interest.

According to the month and year, the time and the interest are not the same.

						Before 01/07/2011 = 35 days / 8%
01/07/2011 - 30/12/2011 = 30 days /: 8,25%
2012 = 30 days : 8%

After, when I load my data, I want to calculate the interest.

So I've got : LOAD Date([Year],'DD/MM/YYYY') as Date_reception,

Delay,

Sum_bill.

But when I want to calculate the interest, I've got this ugly line :

if(([Delay]>30) and ((([Sum_bill.]*if(((num(month([Date]))<7)and (num(year([Date]))=2012)),0.0825,if(((num(month([Date]))<7)and (num(year([Date]))=2011)),0.08,if((num(month([Date]))>=7) and (num(year([Date]))=2012),0.08,if(((num(month([Date]))>=7)and (num(year([Date]))=2011)),0.0825)))))*(([Delay]-30)/365))>5),(([Sum_bill.]*if(((num(month([Date]))<7)and (num(year([Date]))=2012)),0.0825,if(((num(month([Date]))<7)and (num(year([Date]))=2011)),0.08,if((num(month([Date]))>=7) and (num(year([Date]))=2012),0.08,if(((num(month([Date]))>=7)and (num(year([Date]))=2011)),0.0825)))))*(([Delay]-30)/365))) as interets.

Do you have any ideas to lighten up my script ?

Thanks in advance !

Re: Lighten up the script

Tue, 25 Feb 2014 10:59:13 GMT

Antonin,

I would create another table that stores these numbers. Create a calendar or sth like that.

Use intervalmatch (see excellent post by HIC on that topic) so that each date has a Delay and a Interest Rate. You may also compute an interest rate by day, so that you just have to sum(Amount*IR by day)

Afterwards, it should be easier to sum up the interest rate.

Fabrice

Re: Lighten up the script

munna1317 — Tue, 25 Feb 2014 11:26:07 GMT

hi antonin , my advice is to use calender object

Re: Lighten up the script

Tue, 25 Feb 2014 15:18:49 GMT

Thank you for the answers, but now I'm struggling with the Intervalmatch().

The structure is the next :

Mandat :

LOAD Date([Year],'DD/MM/YYYY') as Date_reception,

...

from [.../Délais 2012.xls]

LOAD Date([Year],'DD/MM/YYYY') as Date_reception,

...

from [.../Délais 2013.xls]

Interval :

LOAD * INLINE [

date_from, date_to, taux_journalier, delai

01/01/2008, 31/12/2008, 0.00016411, 45

01/01/2009, 30/06/2009, 0.000246575, 40

01/07/2009, 31/12/2009, 0.000219178, 40

01/01/2010, 30/06/2010, 0.000219178, 35

01/07/2010, 31/12/2010, 0.000219178, 30

01/01/2011, 30/06/2011, 0.000219178, 30

01/07/2011, 31/12/2011, 0.000226027, 30

01/01/2012, 30/12/2012, 0.000219178, 30

01/01/2013, 30/12/2013, 0.000212329, 30

];

Intervalmatch:

IntervalMatch(Date_reception)

load distinct date_from, date_to, taux_journalier,delai Resident Interval;

But it never creates Intervalmatch, and nothing is linked. What did I miss ?

Re: Lighten up the script

pover — Tue, 25 Feb 2014 15:31:03 GMT

Hi Antonin,

I know that this isn't the real script, but make sure the field names match. The date_reception will not match with Date_reception.

Also make sure that the dates in the Inline are being interpreted as dates and not strings. In QlikView do a num(date_from) to see if it returns a number or a null value in the case it is a string. If it is a string load it again as a resident load and use the date#() function to convert the string to date format.

Karl

Re: Lighten up the script

Tue, 25 Feb 2014 15:57:10 GMT

Well, I edited my post, you scared me. I thought that it was just a mistake, and I didn't pay attention to it, but no, in my script, I used the same name everywhere.

And it gaves me a number when I do a num(date_from), : for the 01/01/2009 => 39814. So Qlikview doesn't interprete it as a date, isn't it ?

Re: Lighten up the script

pover — Tue, 25 Feb 2014 16:06:19 GMT

Try the following script:

Mandat :

LOAD Date([Year],'DD/MM/YYYY') as Date_reception,

...

from [.../Délais 2012.xls]

LOAD Date([Year],'DD/MM/YYYY') as Date_reception,

...

from [.../Délais 2013.xls]

Interval :

LOAD * INLINE [

date_from, date_to, taux_journalier, delai

01/01/2008, 31/12/2008, 0.00016411, 45

01/01/2009, 30/06/2009, 0.000246575, 40

01/07/2009, 31/12/2009, 0.000219178, 40

01/01/2010, 30/06/2010, 0.000219178, 35

01/07/2010, 31/12/2010, 0.000219178, 30

01/01/2011, 30/06/2011, 0.000219178, 30

01/07/2011, 31/12/2011, 0.000226027, 30

01/01/2012, 30/12/2012, 0.000219178, 30

01/01/2013, 30/12/2013, 0.000212329, 30

];

Intervalmatch:

IntervalMatch(Date_reception)

load distinct date_from, date_to Resident Interval;

Re: Lighten up the script

Tue, 25 Feb 2014 16:16:06 GMT

Antonin,

As said in the help file:

loadstatement or selectstatement must result in a two-column table,

Taux_Journalier and Delai are too many in the syntax. That is why.

Karl's syntax should work.

Fabrice

Re: Lighten up the script

Tue, 25 Feb 2014 16:49:05 GMT

Thanks a lot ! I wasn't paying attention to that sentence. Shame on me !

Re: Re: Lighten up the script

Tue, 25 Feb 2014 16:58:59 GMT

Hi Antonin, please find the attached qvw for reference.