Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
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;
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
hi antonin , my advice is to use calender object
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 ?
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
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 ?
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;
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
Thanks a lot ! I wasn't paying attention to that sentence. Shame on me !
Hi Antonin, please find the attached qvw for reference.