Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
May be
ApplyMap('MappingTable', Country, 'N/A') as Region
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]);
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.
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
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.
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
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]);
I think you've sussed it, the joker was upper vs lower case data, although I have not checked your code properly.