date range join

[Anonymous]

Hi

I have 2 tables. One with a single date and with 2 dates that define a range.

e.g.

TableA:

Load * inline

[

  ID, Date

  1, '01/01/2015'

  2, '01/02/2016'

  3, '01/09/2016'

  4, '01/02/2014'

];

TableB:

Load * inline

[

  Code,    StartDate,    EndDate

  'A',    '01/01/2014', '01/06/2015'

  'B',    '31/07/2015', '01/03/2016'

  'C',    '01/03/2016', '31/12/2016'

];

I would like to join them using the date in tableA to match the the date range in tableB.

So the end result in the example above would be:

  ID,    Date,          Code

  1,     '01/01/2015',    'A'

  2,     '01/02/2016',    'B'

  3,     '01/09/2016',    'C'

  3,     '01/02/2014',    'A'

Any help appreciated.

Thanks

[maxgro]

look at interval match function in the help or here

IntervalMatch

[sunny_talwar]

Try this with Interval Match function:

TableA:

Load * inline

[

  ID, Date

  1, '01/01/2015'

  2, '01/02/2016'

  3, '01/09/2016'

  4, '01/02/2014'

];

TableB:

Load * inline

[

  Code,    StartDate,    EndDate

  'A',    '01/01/2014', '01/06/2015'

  'B',    '31/07/2015', '01/03/2016'

  'C',    '01/03/2016', '31/12/2016'

];

Left Join (TableA)

IntervalMatch(Date)

LOAD StartDate,

  EndDate

Resident TableB;

Left Join (TableA)

LOAD *

Resident TableB;

DROP Table TableB;

[Anonymous]

Thank you maxgro and Sunny, that is really useful.

I though it would solve the issue, but I've realised that the problem is a bit more complicated than I thought.

The date range needs to be matched only against the tableB records with the same ID.

So the data looks more like this:

TableA:

Load * inline

[

  ID, Date

  1, '01/01/2015'

  2, '01/02/2016'

  3, '01/09/2016'

  4, '01/02/2014'

];

TableB:

Load * inline

[

  ID,   Code,    StartDate,    EndDate

  1,    'A',    '01/01/2014', '01/06/2015'

  1,    'B',    '31/07/2015', '01/03/2016'

  1,    'C',    '01/03/2016', '31/12/2016'

  2,    'D',    '01/01/2014', '01/07/2016'

  3,    'E',    '31/08/2015', '01/01/2016'

  3,    'F',    '01/04/2016', '31/12/2016'

];

and the result I need would be this:

  ID,    Date,          Code

  1,     '01/01/2015',    'A'

  2,     '01/02/2016',    'D'

  3,     '01/09/2016',    'F'

  4,     '01/02/2014',    NULL

Thanks

[sunny_talwar · Accepted solution]

That should be simple. All you need is to use extended interval match (changes in red)

TableA:

Load * inline

[

  ID, Date

  1, '01/01/2015'

  2, '01/02/2016'

  3, '01/09/2016'

  4, '01/02/2014'

];

TableB:

Load * inline

[

  ID,  Code,    StartDate,    EndDate

  1,    'A',    '01/01/2014', '01/06/2015'

  1,    'B',    '31/07/2015', '01/03/2016'

  1,    'C',    '01/03/2016', '31/12/2016'

  2,    'D',    '01/01/2014', '01/07/2016'

  3,    'E',    '31/08/2015', '01/01/2016'

  3,    'F',    '01/04/2016', '31/12/2016'

];

Left Join (TableA)

IntervalMatch(Date, ID)

LOAD StartDate,

  EndDate,

  ID

Resident TableB;

Left Join (TableA)

LOAD *

Resident TableB;

DROP Table TableB;

[Anonymous]

Thank you Sunny

That's just what I need.

[sunny_talwar]

Awesome, I am glad you got what you were looking for. Can you now close this thread by marking the correct response.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny