topic Re: Adding calculated field after IntervalMatch in QlikView

Adding calculated field after IntervalMatch

Wed, 26 Feb 2014 15:28:43 GMT

Hi there !

I'm back with another problem. Yesterday, you helped me with the IntervalMatch concept, but now that I succeed with it, I can't use it.

I searched for an hour now, and I still have trouble with the join functions.

My script is the following :

Mandat :

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

Montant

...

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

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

Montant

...

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;

But now, I want to create a field "IR", which is calculated by (Mandats.Montant * Interval.taux_journalier * Interval.delai).

How can I make it ?

Thanks in advance

Re: Adding calculated field after IntervalMatch

Wed, 26 Feb 2014 16:04:51 GMT

Antonin,

I am interested with the stuff.

Can you upload part of your data files ? Not the qvw (I have the personal edition: I will not be able to open it)

I will build a model and try to get the answer

Thx

Fabrice

Re: Adding calculated field after IntervalMatch

Wed, 26 Feb 2014 16:20:09 GMT

Fabrice,

See in the first post the attachment, with 2 excel files and the txt with the script I'm using.

Thanks

Re: Adding calculated field after IntervalMatch

Wed, 26 Feb 2014 16:55:22 GMT

Antonin,

Yes of course !!

Data loaded. The real job begins ...

Keep you informed

Fabrice
PS: I am learning QV , It is a good exercice for me too

Re: Re: Adding calculated field after IntervalMatch

Wed, 26 Feb 2014 17:44:30 GMT

Antonin,

I did not use IntervalMatch but another method: creation of a line per day between 1st and last day. So that the join is easy.

I changed a little the inline data for test purposes.

I did not know exactly what field to take for the computation of IR but you will adapt the code to your exact needs

Fabrice

Re: Adding calculated field after IntervalMatch

Wed, 26 Feb 2014 18:03:33 GMT

Hey Antonin,

Check out the attached file. It has IR calculated using Intervalmatch on the back-end.

Holler back if you find issues.

Thanks

Re: Adding calculated field after IntervalMatch

Thu, 27 Feb 2014 08:45:41 GMT

Parfait !

Thanks a lot ! You're a better beginner than I am