topic Re: Using ApplyMap() to map two different field names to the same field in QlikView

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

Thu, 03 Sep 2015 07:18:10 GMT

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!

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

qlikmsg4u — Thu, 03 Sep 2015 07:23:15 GMT

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

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

gautik92 — Thu, 03 Sep 2015 07:42:51 GMT

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

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

maxgro — Thu, 03 Sep 2015 18:41:42 GMT

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,

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

sasiparupudi1 — Thu, 03 Sep 2015 19:22:12 GMT

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

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

Thu, 03 Sep 2015 22:23:59 GMT

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?

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

maxgro — Thu, 03 Sep 2015 22:38:33 GMT

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