Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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];
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];