3 Replies Latest reply: Sep 4, 2013 3:01 PM by Manas BN RSS

    ApplyMap with a parameter to use in Where clause

    Manas BN

      Hello all,


      I have a scenario as shown below:

       

      FactTable:

      UserMonthUsage DateProductKey
      U1M19/3/2013P11
      U1M19/3/2013P2

      2

      U1M18/30/2013P13

       

      MappingTable:

      ProductMapped ProductFrom DateTo Date
      P1P29/1/201310/1/2013
      P1P38/1/20138/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:

      UserMonthDateProductKey
      U1M19/3/2013P21
      U1M19/3/2013P2

      2

      U1M18/30/2013P33

       

      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!