Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

Anonymous
Not applicable
Author


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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
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]);

Anonymous
Not applicable
Author

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