Discussion Board for collaboration related to QlikView App Development.
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
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:
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?
We can achieve required result using association and straight table instead of using left join.
Please find attachment for your reference.
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