Using ApplyMap() to map two different field names to the same field

[#1]

Hi, I am trying to use a Mapping Table to do a lookup for a facility code and add a new fields as the facility location.  The facility code is used in several of the fields but they all have different names.  Is there a way to use the ApplyMap() function to map fields that have different names but similar content?  Something like ApplyMap('FacilityTable',FacilityCode=OfficeCode) as OfficeDesc,

Here is my load script:  (Which is returning 0's and -1's in the OrigOfficeDesc and SalesOfficeDesc instead of the lookup field)

FacilityTable:

Mapping Load

     FacilityCode,

     FacilityDesc

FROM

[..\FacilityTable.xlsx]

(ooxml, embedded labels, table is sheet1);

Referrals:

Load

     FacilityCode,

     ApplyMap('FacilityTable',FacilityCode) as OfficeDesc,

     OriginalFacilityCode,

     ApplyMap('FacilityTable',FacilityCode=OriginalFacilityCode) as OrigOfficeDesc

FROM

[..\Referrals.xlsx]

(ooxml, embedded labels, table is sheet1);

Sales:

Load

     SalesFacility,

     ApplyMap('FacilityTable',FacilityCode=SalesFacility) as SalesOfficeDesc,

     DateOfSale,

     SalesAmount

FROM

[..\Sales.xlsx]

(ooxml, embedded labels, table is sheet1);

Obviously my syntax is incorrect in the ApplyMap() functions.  Is it possible to do this?  If so, what is the correct syntax for this operation?

Cheers!

[qlikmsg4u]

did you tried like this?

Referrals:

Load

     FacilityCode,

     ApplyMap('FacilityTable',FacilityCode) as OfficeDesc,

     OriginalFacilityCode,

     ApplyMap('FacilityTable',OriginalFacilityCode) as OrigOfficeDesc

FROM

[..\Referrals.xlsx]

(ooxml, embedded labels, table is sheet1);

will you able to share sample app and expected output

[gautik92]

try lik this

Load

     FacilityCode,

     ApplyMap('FacilityTable',FacilityCode) as OfficeDesc,

     OriginalFacilityCode,

     ApplyMap('FacilityTable',OriginalFacilityCode) as OrigOfficeDesc

FROM

[..\Referrals.xlsx]

(ooxml, embedded labels, table is sheet1);

Sales:

Load

     SalesFacility,

     ApplyMap('FacilityTable',SalesFacility) as SalesOfficeDesc,

     DateOfSale,

     SalesAmount

FROM

[..\Sales.xlsx]

(ooxml, embedded labels, table is sheet1);

[maxgro]

this is the QlikView F1 help

The ApplyMap function is used for mapping any expression to a previously loaded mapping table. The syntax is:

applymap('mapname', expr [ , defaultexpr ] )

where:

mapname is the name of a mapping table that has previously been created through the mapping load or the mapping select statement (see Mapping). Its name must be enclosed by single, straight Quotation Marks in Scripting.

expr is the expression, the result of which should be mapped.

replace    ApplyMap('FacilityTable',FacilityCode=OriginalFacilityCode) as OrigOfficeDesc

with          ApplyMap('FacilityTable',OriginalFacilityCode) as OrigOfficeDesc

replace    ApplyMap('FacilityTable',FacilityCode=SalesFacility) as SalesOfficeDesc,

with          ApplyMap('FacilityTable',SalesFacility) as SalesOfficeDesc,

[sasiparupudi1]

try like this

FacilityTable:

Mapping Load

     FacilityCode,

     FacilityDesc

FROM

[..\FacilityTable.xlsx]

(ooxml, embedded labels, table is sheet1);

Referrals:

Load

     FacilityCode,

     ApplyMap('FacilityTable',FacilityCode) as OfficeDesc,

     OriginalFacilityCode,

     ApplyMap('FacilityTable',OriginalFacilityCode,'NA') as OrigOfficeDesc

FROM

[..\Referrals.xlsx]

(ooxml, embedded labels, table is sheet1);

Sales:

Load

     SalesFacility,

     ApplyMap('FacilityTable',SalesFacility,'NA') as SalesOfficeDesc,

     DateOfSale,

     SalesAmount

FROM

[..\Sales.xlsx]

(ooxml, embedded labels, table is sheet1);

[#6]

I did this and it worked.  Thank you!

But to help my understanding of WHY it worked...if the field in the mapping table is named FacilityCode and the fields I need to apply the map to in the other tables are named OriginalFacilityCode and SalesFacility, ...how does Qlikview know to associate the mapping to the proper field?  Is it not looking at the field name in the mapping table but rather looking at the content of both fields and if one of them matches it returns the other?

[maxgro]

Is it not looking at the field name in the mapping table but rather looking at the content of both fields and if one of them matches it returns the other?

NO, it's looking at the first field and return the second; this is the QlikView F1 help:

A mapping table consists of two columns, the first containing comparison values and the second containing the desired mapping values.

this mapping table  

FacilityTable: Mapping Load    FacilityCode,     FacilityDesc

FROM [..\FacilityTable.xlsx]  (ooxml, embedded labels, table is sheet1);

works in the same way as

FacilityTable: Mapping Load    FacilityCode as FieldA,     FacilityDesc as FieldB

FROM [..\FacilityTable.xlsx]  (ooxml, embedded labels, table is sheet1);