Mapping

[Ethel]

Hello,

my mapping is not working and I cannot find a problem. Could you please help me?

Geographic_mapping:
Mapping Load
Zip_id,
Dual(ZipCodeMap, Geographic_Location_mapped) as geographic_location_detail2
FROM [lib:/.../EM.xlsx]
(ooxml, embedded labels, table is Geographic_Location);

Sites:
Load
City,
ApplyMap('Geographic_mapping', Zip_id) As ZipCodeMapId
FROM [lib://...Sites.qvd] (qvd)
;

My excel EM file(name of the tab Geographic_Location):

Thank you very much in advance!

[tealowk]

Hi @Ethel,

I see that you already have the Zip_id field in your Sites table, which is – as far as I can see from your mapping table – the same as ZipCodeMap.

Thus I assume you want to map the ZIP codes to the Geographic_Location_mapped and suggest that you adjust the mapping as follows:

Geographic_mapping:
Mapping Load
Zip_id,
Geographic_Location_mapped
FROM [lib:/.../EM.xlsx]
(ooxml, embedded labels, table is Geographic_Location);

There is no need to alias field names in the mapping table.  Furthermore, I have removed the dual function, as the arguments did not match the Dual(text, number) syntax.

If it still doesn't work, it may be due to different data types, e.g. if the Zip_id is stored as a string in the Sites table but as integer in the mapping table.

Hope this helps.

[Ethel]

Thank you so much!

For some reason with my original version and now I'm getting an error message that "Zip_id field is not found".

Why it doesn't see it?

Thanks a lot in advance!

[tealowk]

In which table does the error occur? In the Geographic_mapping table or in the Sites table?

[Ethel]

in the Sites table

[tealowk]

That seems weird to me. Can you load the Zip_id field without any transformations? This way we can check if the field is named differently in the QVD or if there are still issues with the mapping.

[Ethel]

What do you mean "load without transformation"?

When I use condition zipcode=53204 in my code, everything is working correctly

[tealowk]

By "load without transformation", I meant loading the data as is. E.g. 'LOAD myField AS zipCode' without the applyMap() function.

---

@Ethel wrote:

When I use condition zipcode=53204 in my code, everything is working correctly

---

Seeing that the condition zipcode=53204 works, maybe the field is called zipcode (opposed to Zip_id) in the Sites QVD file. Have you checked that? 

[tealowk]

When I use condition zipcode=53204 in my code, everything is working correctly

[Ethel]

Do you want me to create qvd or load inline?(what should I use as a source?)