Need to join the dimension table with Transaction with Date should be in between Dates from Dimension - Interval Match Join?

[chandu441]

Hi All,

I have some typical scenario of my requirement.

I make this as simple to understand.

Have to join the transaction table to with Dimension table with left join. I need all the records from transaction table and to get the records many from the dimension We have a key.

Joining is not a big problem, but while joining the transaction date(Date (MM/DD/YYYY)) should falls in between of two dates of Valid From & Valid To Dates of the dimension.

Please see the Tables along with the expected output table screenshot.

Attached Excel for test purpose.

[edwin · Accepted solution]

try this:

trans:
load * inline [
Sold To Customer ID,Sold To Customer Sales Org,DISTRIBUTIONAL CHANNEL,Date,Invoice Number,Invoice Line,MPG
9036376,CAS6,10,4/1/2021,2200000536,1,J7
9036376,CAS6,10,4/1/2021,2200000536,1,J7
9036376,CAS6,10,4/7/2021,2200000537,1,J7O
9036376,CAS6,10,4/7/2021,2200000537,1,J7O
];

left join (trans)
load * inline [
Sold To Number,SALES ORG,DISTRIBUTIONAL CHANNEL,MPG,Partner/Agent Number,Partner/Agent Name,Valid From,Valid To
9036376,CAS6,10,J7O,30003839,PARTS - EMERGENCY LIGHTING AGENT,04/01/2005,12/31/9999
9036376,CAS6,10,J7O,30003424,PAUL LEWANDOWSKI-CA,04/01/2005,12/31/9999
9036376,CAS6,10,J7O,30003424,SATYA JAN LEE,04/01/2005,12/31/9999
9036376,CAS6,10,J8V,30003839,PARTS - EMERGENCY LIGHTING AGENT,02/24/2010,12/31/9999
9036376,CAS6,10,JFG,30003839,PARTS - EMERGENCY LIGHTING AGENT,02/24/2010,12/31/9999
9036376,CAS6,10,J4,30003440,OMNILUMEN - EG,11/02/2020,12/31/9999
9036376,CAS6,10,J5,30003440,OMNILUMEN - EG,11/02/2020,12/31/9999
9036376,CAS6,10,J6,30003440,OMNILUMEN - EG,11/02/2020,12/31/9999
9036376,CAS6,10,J7,30003440,OMNILUMEN - EG,11/02/2020,04/05/2021
9036376,CAS6,10,J7,30003839,PARTS - EMERGENCY LIGHTING AGENT,04/06/2021,04/08/2021
9036376,CAS6,10,J7,30003424,PAUL LEWANDOWSKI-CA,04/09/2021,12/31/9999
];


NoConcatenate
newTrans:
load * Resident trans where Date >= [Valid From] and Date <= [Valid To];