Require help with Join syntax for resident tables

[vishnus85]

Hi,

I am facing a problem with joining two resident tables in qlikview.

Table A has fields

SlNO

START_DATE

STOP_DATE

Table B has fields

SINO

ADMIT_DATE

I want to join the bigger table A to smaller table B such that, B would be joined to A on

SINO

and only when B.ADMIT date between A.START_DATE and A.STOP_DATE

Kindly guide me.

Regards,

Vishnu S

[hectorgarcia]

Have you tried intervalmatch????

Sent from my iPhone

[eduardo_sommer]

Maybe this:

TempTable:

Load SINO,

START_DATE,

STOP_DATE

Resident TableA;

join (TempTable)

  Load SINO,

          ADMIT_DATE

  Resident TableB;

FinalTable:

Load SINO

Resident TempTable

Where ADMIT_DATE >= START_DATE

  And  ADMIT_DATE <= STOP_DATE;

Drop Table TempTable;

After this lines, you will have three tables. The original Tables TableA and TableB, plus a new table, FinalTable, with only one field SINO, containing those SINO whose ADMIN_DATE is between START_DATE and STOP_DATE. All three tables linked by field SINO.

I hope this helped you

Eduardo

[hic · Accepted solution]

This is exactly what IntervalMatch does. See more here:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

HIC

[eduardo_sommer]

Yes, Henric

I realized that after sending my answer.

Thank you

Eduardo

[vishnus85]

Thanks Henric and Eduardo !!