ApplyMap with a parameter to use in Where clause

[manas_bn]

Hello all,

I have a scenario as shown below:

FactTable:

User	Month	Usage Date	Product	Key
U1	M1	9/3/2013	P1	1
U1	M1	9/3/2013	P2	2
U1	M1	8/30/2013	P1	3

MappingTable:

Product	Mapped Product	From Date	To Date
P1	P2	9/1/2013	10/1/2013
P1	P3	8/1/2013	8/31/2013

Based on the "Usage Date" field in the Fact table I have to pick the Mapped Product in MappingTable.

So the End Table should look like:

User	Month	Date	Product	Key
U1	M1	9/3/2013	P2	1
U1	M1	9/3/2013	P2	2
U1	M1	8/30/2013	P3	3

Is there a way I can use ApplyMap to pass the UsageDate field as a parameter to use in the where clause and build a dynamic mapping table?

map:

mapping load

Product, MappedProduct

Resident MappingTable

where From Date<=(VariableDate) and ToDate>=(VariableDate); //VariableDate is the Usage Date in Fact Table.

Also is there any other direction I need to go with to achieve this (preferably avoiding joins)?

Thanks in advance for your help!

[vikasmahajan · Accepted solution]

You need to apply Interval Match Function instead of Apply Map , See demo attached.

Hope this helps you.

Vikas

[er_mohit]

See the attached pdf

[manas_bn]

Thanks a lot Vikas, Mohit! This is exactly what I needed!