Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
rudywelvaert
Creator
Creator

Join tables on id and dates

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_IDSalesman_IDSale_DateSale_Amount
1112/07/2015125,25
221/01/201670,33
3222/06/2016312,18
411/07/201699,09
531/07/201687,35
6224/11/201617,41
711/01/2017365,11
8217/04/201724,00
922/06/2017181,74
10125/07/2017147,05

   

Salesmen
ID_SalesmanSalesmanID_SalesAdvisorSalesAdvisorSales_QuotumDate_FromDate_Till
1John1Charles0,681/01/201615/08/2016
1John4Dirk0,7816/08/20163/02/2017
1John P.2Patrick0,713/02/2017
2Michael3Marian0,8018/02/201623/11/2016
2Michael D.3Marian0,811/06/2017
2Michael D.4Dirk0,751/01/201731/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_IDSale_DateSale_AmountID_SalesmanSalesmanSales_QuotumID_SalesAdvisorSalesAdvisor
112/07/2015125,25-----
21/01/201670,33-----
322/06/2016312,182Michael0,803Marian
41/07/201699,091John0,681Charles
51/07/201687,35-----
624/11/201617,41-----
71/01/2017365,111John0,784Dirk
817/04/201724,002Michael D.0,754Dirk
92/06/2017181,742Michael D.0,813Marian
1025/07/2017147,051John P.0,712Patrick

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.

 

1 Solution

Accepted Solutions
Kushal_Chawda

6 Replies
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

Great dreamer's dreams never fulfilled, they are always transcended.
Kushal_Chawda

rudywelvaert
Creator
Creator
Author

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
Contributor III
Contributor III

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
Creator
Creator
Author

Thanks Kushal Chawda,

This is te sollution I was looking for.

Seems to work

rudywelvaert
Creator
Creator
Author

Thanks Paul,

I would like indicate your solution also as Correct!

I can only indicate one

R.W.