topic Re: Join based on date in QlikView

Join based on date

pauldamen — Wed, 20 Apr 2016 09:29:22 GMT

Dear all,

I have 2 tables, one table with a key, field value and date. And a second table which needs to be joined on:

Table 1 (needs to be joined to table 2)

Key, Date, field

A 1/1 10

A 1/4 12

B 1/1 8

C 1/1 10

The Field needs to be joined to table to but based on the date. So for Key A all transactions in table 2 needs the field value 10. Until 1/4 after that all the field values need to be joined with 12. etc.

How can I achieve this.

Regards, Paul

Re: Join based on date

sunny_talwar — Wed, 20 Apr 2016 09:31:23 GMT

You need to use Extended Interval Match here

IntervalMatch

IntervalMatch and Slowly Changing Dimensions

Qlikview Tutorial: Extended IntervalMatch

Re: Join based on date

swuehl — Wed, 20 Apr 2016 09:40:51 GMT

I think you need to start with

Creating a Date Interval from a Single Date

then you can INTERVALMATCH your field values as described in the referenced links.

Re: Join based on date

nico_ilog — Wed, 20 Apr 2016 10:17:33 GMT

The Intervalmatch would be your best bet as indicated by Sunny T.

Your could create a newID and link the tables on that way.

Table1:

Key, Date, field

A 1/1 10

A 1/4 12

B 1/1 8

C 1/1 10

Your new ID field would then be Key&'-'&Date&'-'&field as newID

Then you do the same with Table2 (Key&'-'&Date&'-'&field as newID).

Make sure that NewID is the one linking the two tables (i.o.w. Change the field names to not correspond, to avoid synthetic keys) and use the Date field from one table as the primary date field.

It's a dirty solution!! But it's worked before as a quick workaround.

Nico