Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

laylagirl
New Contributor III

Any suggestions on how to applymap correctly ?

Hello,

Here is my load script :

LOAD

Country

FROM [lib://AttachedFiles/Updated data files.xlsx]

(ooxml, embedded labels, table is [Table 1]);

LOAD

    CountryC,

    RegionTS

FROM [lib://AttachedFiles/REGIONS.xlsx]

(ooxml, embedded labels, table is [Table2]);

Need to show Country from Table1 as RegionTS in Table2

Here is my applymap script:

MappingTable:

Mapping

LOAD CountryC,

    Region

FROM

[lib://AttachedFiles/REGIONS.xlsx]

(ooxml, embedded labels, table is [Table2]);

Fact:

LOAD Country,

ApplyMap('MappingTable', RegionTS, 'N/A') as Region

FROM

[lib://AttachedFiles/Updated data files.xlsx]

(ooxml, embedded labels, table is [Table 1]);

Script is not working. Any suggestions ?

8 Replies
antoniotiman
Honored Contributor III

Re: Any suggestions on how to applymap correctly ?

May be

ApplyMap('MappingTable', Country, 'N/A') as Region

Re: Any suggestions on how to applymap correctly ?


Maybe something like this :



MappingTable:

Mapping

LOAD CountryC,

    RegionTS

FROM

[lib://AttachedFiles/REGIONS.xlsx]

(ooxml, embedded labels, table is [Table2]);

Fact:

LOAD Country,

ApplyMap('MappingTable', Country, 'N/A') as Region

FROM

[lib://AttachedFiles/Updated data files.xlsx]

(ooxml, embedded labels, table is [Table 1]);

fdiaspp-curitib
New Contributor II

Re: Any suggestions on how to applymap correctly ?

Hello Layla!

On file '[lib://AttachedFiles/Updated data files.xlsx]' there is no field called 'RegionTS'. The ApplyMap function needs the data exists on the load context. The result of the expression that you will use on ApplyMap has to be equivalent on the CountryC field that is present on MappingTable.

laylagirl
New Contributor III

Re: Any suggestions on how to applymap correctly ?

Bill

When I implement a REgion filter it only shows NA as a value. Need the countries in table auto grouped into Regions instead.

Another issue is when I make a selection on new Region filter my bar charts are not reacting

Re: Any suggestions on how to applymap correctly ?

Could you attach sample xlsx's ?

Maybe your country data does not match between your xlsx's , although I can only guess without any sample data.

laylagirl
New Contributor III

Re: Any suggestions on how to applymap correctly ?

Ok I attached them on top of original post. Please take a look.

The script loads error free and there's not errors in the data model viewer.

Just that my bar chart doesn't react to the any selections made in the new Regions filte

antoniotiman
Honored Contributor III

Re: Any suggestions on how to applymap correctly ?

May be this

MappingTable:
Mapping
LOAD Upper(CountryC) as CountryC,
RegionTS
FROM
https://community.qlik.com/servlet/JiveServlet/download/1386387-304702/Table2.xlsx
(ooxml, embedded labels, table is Sheet1);

LOAD LeadNoteId,
ProspectNum,
LeadNoteSeq,
City,
State,
Country,ApplyMap('MappingTable', Country, 'N/A') as Region

FROM
https://community.qlik.com/servlet/JiveServlet/download/1386387-304701/Table1.xlsx
(ooxml, embedded labels, table is
[Order Data No Dups]);

Re: Any suggestions on how to applymap correctly ?

I think you've sussed it, the joker was upper vs lower case data, although I have not checked your code properly.