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

chandu441 — Mon, 26 Apr 2021 17:13:45 GMT

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.

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

edwin — Tue, 27 Apr 2021 21:30:37 GMT

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];

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

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

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