topic Re: Mapping Load in Connectivity & Data Prep

Mapping Load

pvragel — Fri, 02 Apr 2021 07:49:10 GMT

I have several files. Each file has Company Name as one of the fields.

One file has the Company Names that I want to use. (e.g., Smith Co., Inc). The other files have variations on the desired name (e.g., Smith Co ; Smith Company ; etc.)

I am trying to use the Mapping Load function to map all the variations of the name to the desired name, so I can use that name in all the linking and analysis.

I created a mapping file (called alt_names.xlsx) with alternate names in the first column (x), and the desired name in the second column (y).

I used the following statement to load the mapping file:

Mapping Load x,y from alt_names.xlsx

(ooxml,embedded labels, table is Sheet1);

After loading this file, the Company Name list in the Qlikview file shows both names, rather than the desired company name, and I am not getting the linking between the information for the specific company in the files.

I would appreciate suggestions to correct this situation. Thanks in advance.

Re: Mapping Load

marcus_sommer — Tue, 02 Oct 2018 13:32:32 GMT

You need to use applymap() to match the data from the mapping-table, see: Mapping … and not the geographical kind.

- Marcus

Re: Mapping Load

pvragel — Wed, 03 Oct 2018 15:48:48 GMT

Hi Marcus,

Thank you for pointing me in the right direction.

Based on the info in the link, I have the following script:

CompanyMap:

Mapping Load RefCode,[Company Name] from alt_names.xlsx

(ooxml, embedded labels, table is Sheet1);

alt_names:

Load

ApplyMap('CompanyMap',[Company Name]) as [Company Name]

from alt_names.xlsx

(ooxml, embedded labels, table is Sheet1);

alt_names.xlsx is the file with the first column (RefCode) being the alternate names that may appear in files, and the second column (Company Name) being the desired Company Name.

This script runs without error, but does not produce the desired result. I tried removing all the data and reloading, this also did not produce the desired result.

Does the position in the script matter? I tried at both the beginning of the script and at the end of loading all the other files - neither produced the desired result.

What am I missing? Thanks for your help.

Re: Mapping Load

CarlosAMonroy — Wed, 03 Oct 2018 15:56:54 GMT

Hi Paul

You're missing the target table where you're going to apply the map. If you see your script, you're apllying the map to the same mapping table.

the structure for the mapping is:

MapTable1:

Mapping Load KeyField, FieldToMap From Table1;

Table2:

Load KeyField, Applymap('MapTable1',KeyField) as FieldFromMap From Table2;

Hope that helps, thanks

Carlos

Re: Mapping Load

stevedark — Wed, 03 Oct 2018 15:58:59 GMT

Hi Paul,

When you apply the map you are providing the company name, not the ref.

It should read:

ApplyMap('CompanyMap',CompanyRef) as [Company Name]

Hope that helps,

Steve

Re: Mapping Load

marcus_sommer — Wed, 03 Oct 2018 16:12:17 GMT

From the syntax it looks ok. but I think logically it should look more like this:

CompanyMap:

Mapping Load [Company Name] as LookupValue, RefCode as ReturnValue

from alt_names.xlsx (ooxml, embedded labels, table is Sheet1);

MainTable:

Load *,

ApplyMap('CompanyMap', [Company Name], '#NV - ' & [Company Name]) as [Company Name New]

from MainData.xlsx (ooxml, embedded labels, table is Sheet1);

Means you need to load your main-data and replacing there the wrong names with the correct ones. Further, the fieldnames within a mapping-table are not important but the order is (I highlighted this order).

- Marcus

Re: Mapping Load

pvragel — Sat, 06 Oct 2018 16:57:19 GMT

I renamed the columns in the mapping file.

the first column is RefCode (these are the variations on the company names)

the second column is WICoName (this is the [Company Name] that I want to use

Interpreting your instructions I loaded the following code:

//load everything in the main file

Directory;

LOAD *

FROM

stamp_from_directory.xlsx

(ooxml, embedded labels, table is Detail2018100109293780);

//load the mapping file

CompanyMap:

Mapping Load RefCode, WICoName From alt_names.xlsx

(ooxml, embedded labels, table is Sheet1);

//apply the map

stamp_from_directory:

Load [Company Name],

ApplyMap('CompanyMap',WICoName) as [Company Name] From

stamp_from_directory.xlsx

(ooxml, embedded labels, table is Detail2018100109293780);

I received the following error message:

Field not found - <WICoName>

stamp_from_directory:

Load [Company Name],

ApplyMap('CompanyMap',WICoName) as [Company Name] From

stamp_from_directory.xlsx

(ooxml, embedded labels, table is Detail2018100109293780)

The WICoName field name (from the alt_names file) is correct.

Can you advise where I am going wrong? Thanks.

Re: Mapping Load

marcus_sommer — Sun, 07 Oct 2018 10:11:21 GMT

A mapping has the following logic and order:

mapTable: mapping load Lookup, Return

from MapSource;

mainTable: load * /* AllNeededFields */, applymap('mapTable', Lookup, Default) as Changed

from MainSource;

All loaded fields must be existing within the sources, the fieldnames within the mapping-table aren't important, only the order of them is and that it are two fields. The applymap() is executed within the main-table and need to contain the map-name, the Lookup and optional a default-value.

- Marcus