Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
chandu441
Creator
Creator

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

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.

Labels (2)
1 Solution

Accepted Solutions
edwin
Master II
Master II

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

View solution in original post

1 Reply
edwin
Master II
Master II

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