Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
laylagirl
Contributor III
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
Master III
Master III

May be

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

bill_markham


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
Contributor II
Contributor II

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
Contributor III
Contributor III
Author

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

bill_markham

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
Contributor III
Contributor III
Author

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
Master III
Master III

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

bill_markham

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