Join tables on id and dates

[rudywelvaert]

Hi,

I need to join two table using an id (key) and a date.

The base table Sales which needs to be extended contains among others a Sale_ID (PK), a Salesman_ID (FK) and a Sale_Date (FK)

The tabel that needs to be linked contains among others an ID_SalesMan, a Date_From and a Date_Till

Sales
Sale_ID	Salesman_ID	Sale_Date	Sale_Amount
1	1	12/07/2015	125,25
2	2	1/01/2016	70,33
3	2	22/06/2016	312,18
4	1	1/07/2016	99,09
5	3	1/07/2016	87,35
6	2	24/11/2016	17,41
7	1	1/01/2017	365,11
8	2	17/04/2017	24,00
9	2	2/06/2017	181,74
10	1	25/07/2017	147,05

Salesmen
ID_Salesman	Salesman	ID_SalesAdvisor	SalesAdvisor	Sales_Quotum	Date_From	Date_Till
1	John	1	Charles	0,68	1/01/2016	15/08/2016
1	John	4	Dirk	0,78	16/08/2016	3/02/2017
1	John P.	2	Patrick	0,71	3/02/2017
2	Michael	3	Marian	0,80	18/02/2016	23/11/2016
2	Michael D.	3	Marian	0,81	1/06/2017
2	Michael D.	4	Dirk	0,75	1/01/2017	31/05/2017

If records are found in table Salesmen on basis of Sales.Salesman_ID = Salesmen.ID_Salesman, only the record is linked where Sales.Sale_Date lays between Salesmen.Date_From and Salesmen.Date_Till.

In all other cases only the data from Sales is kept, the fields coming from Salesmen are empty.

This should be the result:

Sale_ID	Sale_Date	Sale_Amount	ID_Salesman	Salesman	Sales_Quotum	ID_SalesAdvisor	SalesAdvisor
1	12/07/2015	125,25	-	-	-	-	-
2	1/01/2016	70,33	-	-	-	-	-
3	22/06/2016	312,18	2	Michael	0,80	3	Marian
4	1/07/2016	99,09	1	John	0,68	1	Charles
5	1/07/2016	87,35	-	-	-	-	-
6	24/11/2016	17,41	-	-	-	-	-
7	1/01/2017	365,11	1	John	0,78	4	Dirk
8	17/04/2017	24,00	2	Michael D.	0,75	4	Dirk
9	2/06/2017	181,74	2	Michael D.	0,81	3	Marian
10	25/07/2017	147,05	1	John P.	0,71	2	Patrick

In attachment I added a qvw with the basic tables and a try to get the two tables joined. But I only get the sales records for which I find a salesmen with same id and a date between the two dates in salesmen.

Anyone who knows how I can solve this in QlikView?

Thanks

R.W.

[PrashantSangle]

Hi,

We can achieve required result using association and straight table instead of using left join.

Please find attachment for your reference.

Regards,

Prashant

[Kushal_Chawda · Accepted solution]

see the attached

[rudywelvaert]

Thanks Prashant Sangle

This is definately a  sollution if the end result is the Table Box

But I need a table that can be used in further elaboration

R.W.

[paulferguson80]

Hi Rudy,

I would suggest the interval match function to be used on the data, as this will appropriate the rows correctly with the start and end dates for the sales record.

This will also allow you to re use the ID's with changes in sales people over time and the logic will give you what you expect the result to be in your question.

If you have the same problem with the sales advisers changing over time, you can re use the process on the advisor_id as well allowing further flexibility.

I have updated and re attached the sample with notes so you can see what the method and output is.

Hope it gives you what you need

Ta Paul

[rudywelvaert]

Thanks Kushal Chawda,

This is te sollution I was looking for.

Seems to work

[rudywelvaert]

Thanks Paul,

I would like indicate your solution also as Correct!

I can only indicate one

R.W.