Create a join with IntervalMatch and a key

Mon, 16 Mar 2015 13:49:02 GMT

Hello,

I am trying to do the same thing as this, but with a script:

Create table #table(DIVI varchar(3), Code_devise varchar(3), DateValidite_Devise datetime, Taux_devise decimal(15,5))

INSERT into #table

Select '1Z0', 'USD', '2015-01-01 00:00:00.000', 1.30

UNION ALL Select '1Z0', 'USD', '2015-02-01 00:00:00.000', 1.20

UNION ALL Select '1Z0', 'USD', '2015-03-01 00:00:00.000', 1.10

UNION ALL Select '1Z0', 'USD', '2015-04-01 00:00:00.000', 1.00

UNION ALL Select '1Z0', 'GBP', '2015-01-01 00:00:00.000', 0.9

UNION ALL Select '1Z0', 'GBP', '2015-03-01 00:00:00.000', 0.95

UNION ALL Select 'AAA', 'USD', '2015-01-01 00:00:00.000', 1.00

Select DIVI

,Code_devise

,Taux_devise

, DateValidite_Devise as StartDate

, (

SELECT ISNULL(MIN(EndTable.DateValidite_Devise),'2099-01-01 00:00:00.000')

from #table EndTable

WHERE StartTable.DIVI = EndTable.DIVI

AND StartTable.Code_devise = EndTable.Code_devise

AND StartTable.DateValidite_Devise < EndTable.DateValidite_Devise

) AS EndDate

from #table StartTable

--WHERE StartTable.DateValidite_Devise = '2015-03-01 00:00:00.000'

DROP table #table

So I have a table with a starting date, but no end date.

The end date is the next period starting date...

I am trying create a table with both start and end date.

In my script I have:

devises:

LOAD

CUDIVI as %DIVI,

CUCUCD as Code_devise,

date#(CUCUTD,'YYYYMMDD') as DateValidité_Devise,

CUARAT as Taux_devise

FROM CCURRA.QVD (qvd)

WHERE

CUCRTP=4

;

Fin:

LOAD

Text(%DIVI)&Code_devise as clef,

%DIVI as %DIVIFin,

Code_devise as Code_deviseFin,

DateValidité_Devise as DateValidité_DeviseFin,

Taux_devise as Taux_deviseFin

Resident devises

;

Debut:

LOAD

Text(%DIVI)&Code_devise as clef,

%DIVI as %DIVIDebut,

Code_devise as Code_deviseDebut,

DateValidité_Devise as DateValidité_DeviseDebut,

Taux_devise as Taux_deviseDebut

Resident devises

;

TmpLien:

INNER JOIN (Debut)

IntervalMatch(DateValidité_DeviseDebut)

LOAD 0, DateValidité_DeviseFin

Resident Fin

WHERE clef = '1Z0CHF'

AND DateValidité_DeviseDebut = DateValidité_DeviseFin

;

My problem is that I can join on:

- the same name between the 2 tables

- using IntervalMatch

But not both in, the same time.

Could you help me or give me another way of doing that ?

Thank you,

Willy

Re: Create a join with IntervalMatch and a key

Gysbert_Wassenaar — Tue, 17 Mar 2015 13:29:42 GMT

It's not clear to me what you're trying to do. But perhaps you can use the extended syntax of the intervalmatch function. It has its own entry in the help file IntervalMatch (Extended Syntax).

topic Create a join with IntervalMatch and a key in QlikView

Create a join with IntervalMatch and a key

Re: Create a join with IntervalMatch and a key