Join based on date

[pauldamen]

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

[sunny_talwar]

You need to use Extended Interval Match here

IntervalMatch

IntervalMatch and Slowly Changing Dimensions

Qlikview Tutorial: Extended IntervalMatch

[swuehl]

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.

[nico_ilog]

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