Apply Map Issue

[#1]

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

[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

[swuehl]

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?