Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
see the attached
Hi,
We can achieve required result using association and straight table instead of using left join.
Please find attachment for your reference.
Regards,
Prashant
see the attached
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.
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
Thanks Kushal Chawda,
This is te sollution I was looking for.
Seems to work
Thanks Paul,
I would like indicate your solution also as Correct!
I can only indicate one
R.W.