topic Re: date range join in QlikView

date range join

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: date range join

maxgro — Wed, 05 Oct 2016 16:09:57 GMT

look at interval match function in the help or here

IntervalMatch

Re: date range join

sunny_talwar — Wed, 05 Oct 2016 16:11:21 GMT

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;

Re: date range join

Anonymous — Thu, 06 Oct 2016 09:02:11 GMT

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

Re: date range join

sunny_talwar — Thu, 06 Oct 2016 17:08:24 GMT

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,

Resident TableB;

Left Join (TableA)

LOAD *

Resident TableB;

DROP Table TableB;

Re: date range join

Anonymous — Fri, 07 Oct 2016 08:20:27 GMT

Thank you Sunny

That's just what I need.

Re: date range join

sunny_talwar — Fri, 07 Oct 2016 15:57:53 GMT

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