Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
fabionogarotto
Contributor II
Contributor II

Join with date range

Hello all,

it's my first app in Qlik Sense, and I'm trying to understand how table associations work. 

I have two tables:

InvoiceRows (InvoiceDate, ProductID, RowType, Quantity, Price)

PurchasePrice (ProductID, StartDate, EndDate, Price)

How can I join those two tables, to know the purchase price valid at the InvoiceDate (e.g. InoviceDate must be included between StartDate and EndDate for the same ProductID)?

I would like that if a product in the invoice has no valid purchase price, it does not appear (e.g. left join).

Thank you very much!

Fabio

2 Replies
martinpohl
Partner - Master
Partner - Master

Hi,

use

Inner Join IntervalMatch ( InvoiceDate, ProductID) LOAD StartDate, EndDate , Price Resident PurchasePrice ;

 

See 

https://help.qlik.com/en-US/sense/June2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes...

Regards

fabionogarotto
Contributor II
Contributor II
Author

Hello,

thank you for your reply! 

The third parameter of LOAD shouldn't it be the ProductID, such as: 

Inner Join IntervalMatch ( InvoiceDate, ProductID) LOAD StartDate, EndDate , ProductID Resident PurchasePrice ; 

Thank you!