Join with date range

[fabionogarotto]

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

[martinpohl]

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]

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!