Mapping with ApplyMap

[Zohar_B]

Hi, I am trying to use ApplyMap function in a script. For this I created a table which I loaded from an Excel file.
Then I tried to use the Mapping Load attribute. I get always the error "Map_ID"not found or the field name not found.
Here is what I am doing:

LOAD //table name is Hospitals
Text(BCode) as BCode,
Hospital_Name,
text(Hospital_ID) as Hospital_ID,
IsIshpuz,
Data
FROM [lib://DataFiles/Hospitals.xlsx]
(ooxml, embedded labels, table is Hospitals)

Hosp_Map: // for mapping purposes
Mapping Load
Text(BCode) as BCode,
Hospital_Name as HName
Resident Hospitals;
Drop Table Hospitals; //without this statement - I can't see the Hosp_Map table in the data model

Load
*,
ApplyMap('Hosp_Map',Hname) as Hosp_name

The idea is to bring the full name using Vlookup given BCode.

What am I missing or doing wrong?

Is there another way to achieve this in the Visualization part (Set Expression or something like that)?

Thanks for detailed help. 🙂

[Gabbar]

Load
*,
ApplyMap('Hosp_Map',Hname) as Hosp_name:


The possible reason is that Hname doesnt exist here.

[Zohar_B]

Hname exists in the resident table definition:
Mapping Load
Text(BCode) as BCode,
Hospital_Name as HName
Resident Hospitals;
Drop table Hospitals (this seems to be necessary otherwise I can't see the mapping talbe)

I added Exit Script statement to check the table. It was there.

Something else must  be the reason. Any idea?

[Zohar_B]

In all examples I found - Mapping Load is used with INLINE table. Can I use it with loaded table or resident table?

[Zohar_B]

My next question is the following:
I want to use the table I created for mapping, therefore I need to prefix "NOCONCATINATE" prefix but at the same time I want to use the resulting table for mapping, therefore I need to use the "MAPPING" prefix as well, but it doesn't work! Any way around this? Thanks.

[BrunPierre]

Hi @Zohar_B  Both the mapping table and the destination table must contain the comparing values (BCode).

Load
*,
ApplyMap('Hosp_Map', BCode,'Unspecified') as Hosp_name

Whether it's a loaded table or a resident table, it must always consist of two columns: the first column for holding comparison values, and the second column for storing the corresponding desired mapping values.

If you still need the 'Hospital' table, keep in mind that the 'Hosp_Map' table is only temporarily stored in memory during script execution and will be automatically removed later. Therefore, you can leave out the drop statement if you wish to retain the 'Hospital' table.

[Zohar_B]

Hi @BrunPierre , thanks for your enlightening reply, it helped partially.
In the second table which I load and use the applymap function, I need also to check another condition.
It looks like this:
ISHPUZ:
Load 
Data1, Data2, Data3,
Data4 as BCode,
Applymap('Hosp_Map',Bcode,'No ') as Hosp_name
From {QVD data source}
Where Exists(Data4,Bcode)

I receive an error message "Field Bcode not found", although the mapping table is in the memory.
I tried other versions like : Where Exists (Data4) result is no records were loaded, Where exists (Bcode) error message  and even Where exists (Bcode,Data4). 
None worked. I understand that the mapping table is temporary and loaded in the memory but the Applymap functions knows how to look into it and perform the vlookup I need, so why the rest of the statement is not legal?
Any suggestions or advise? Thanks in advance

[anat]

can you replace Data4 inplace of Bcode in applymap

Applymap('Hosp_Map',Data4,'No ') as Hosp_name

[Zohar_B]

Applymap works fine. The problem is that I need to load only records where the Bcode (hospital code) exists in the Hospital table (Hosp_Map) but it seems that the field names are not there. So I might load the Hosp_Map file twice, once for mapping and once for verifying existence.  But it doesn't allow me to do so... So I am stuck.

[BrunPierre · Accepted solution]

'The error occurs because the Where Exist clause is unable to find "Bcode" in the table "ISHPUZ". By creating a resident table, you allow the ApplyMap() function to access the values from the mapping table without issues in the Where Exists clause.