2 Replies Latest reply: Jan 23, 2016 6:19 AM by Stefan Wühl RSS

    Apply Map Issue

      Hey guys,

      I'm running into trouble with the Apply Map function. The data that is being pulled into the Apply Map is not matching what is in the original QVD. For example, in my Site.qvd I have two rows, SiteId and SourceData - SiteId is an Id filed and SourceData is a value. When I use the ApplyMap function below, the Id field is returned. Can some explain what I'm doing wrong?

       

      Here is my script.

       

       

      SiteSourceMap:

      Mapping LOAD

          SiteId,

          SourceData

      FROM [lib://Development Final Transformed Data/Site.qvd]

      (qvd);

       

      [OrderDetailFact]:

      Load

        OrderId,

          //ProjectId,

          SourceData & '-' & SiteId as SiteKey,

          SourceData & '-' & "UserId" as UserKey;

          //CreationDate;

      LOAD

          ID as OrderId,

          AlternateId,

          ProjectId,

          ApplyMap('SiteSourceMap', SiteId) as SourceData,

          SiteId,

          "UserId",

          UserEmail,

          OrderNumber,

          ClientCurrencyCode

      FROM [lib://Development Raw Managed Data/Order_Order.qvd]

      (qvd);

        • Re: Apply Map Issue
          Marcus Sommer

          You need to specify a third parameter to applymap like:

           

          ApplyMap('SiteSourceMap', SiteId, 'AlternativeReturnValueIfNoMappingValueCouldBeFound')

           

          This meant your SiteId seems not identically in both tables - maybe SiteId needs a cleaning like trim() or converting with floor() or formating with num() to be matchable.

           

          - Marcus

          • Re: Apply Map Issue
            Stefan Wühl

            You can also start with just linking the two tables by SiteId:

             

            SiteSource:

            LOAD

               SiteId,

                SiteId as SiteIdSource,

                SourceData

            FROM [lib://Development Final Transformed Data/Site.qvd]

            (qvd);

             

            [OrderDetailFact]:

            LOAD

                ID as OrderId,

                AlternateId,

                ProjectId,

            //   ApplyMap('SiteSourceMap', SiteId) as SourceData,

                SiteId as SiteIdOrder,

                SiteId,

                "UserId",

                UserEmail,

                OrderNumber,

                ClientCurrencyCode

            FROM [lib://Development Raw Managed Data/Order_Order.qvd]

            (qvd);

             

            Then load and create a table box in your front end, using OrderId, SiteIdOrder, SiteId, SiteIdSource as fields in the table. Order by SiteId.

            Check where the SiteId* fields in both tables match (located in one row) and where they don't match.

             

            Are you able to see why they don't match? Maybe there are leading zeros missing in one or the other field?