Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Lighten up the script

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 !

1 Solution

Accepted Solutions
pover
Honored Contributor

Re: Lighten up the script

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;

9 Replies
Not applicable

Re: Lighten up the script

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

munna1317
Contributor II

Re: Lighten up the script

hi antonin , my advice is to use calender object

Not applicable

Re: Lighten up the script

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 ?

pover
Honored Contributor

Re: Lighten up the script

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

Not applicable

Re: Lighten up the script

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 ?

pover
Honored Contributor

Re: Lighten up the script

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;

Not applicable

Re: Lighten up the script

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

Not applicable

Re: Lighten up the script

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

Not applicable

Re: Re: Lighten up the script

Hi Antonin, please find the attached qvw for reference.

Community Browser