topic Re: Mapping Require 2 column in QlikView

Mapping Require 2 column

Vincent24 — Wed, 25 Nov 2020 16:16:04 GMT

Attempting to use ApplyMap to search for matching Data from the first table called 'InventoryList' and the field name containing the Data is called 'PMDescription'. The second table contains multiple fields that has the same I would like to join and I keep getting MAPPING requires 2-column input. What is the propoer syntax. Works fin when I try to map a single field.

Example:
Directory;
SapSearchTable:
Mapping LOAD
PMDescription,
AssetBarCode,
Username

FROM
[Fixed Asset Register Recon Jan WIP For Thabo Data-Man.xlsm]
(ooxml, embedded labels, table is InventoryList);

Directory;
FoundTable:
LOAD PartNumber,
ApplyMap('SapSearchTable',PartNumber,0) as PartNumber1,
[SAP Asset Number],
Subnumber,
ApplyMap('SapSearchTable',Subnumber,'NA') as Subnumber2,
[Capitalized on],
ApplyMap('SapSearchTable', [Capitalized on],'NA') as [Capitalized on1],
Description,
ApplyMap('SapSearchTable', Description,'NA') as Description2,
Inventorynumber,
ApplyMap('SapSearchTable', Inventorynumber,'NA') as Inventorynumber2,
PartNumber1,
ApplyMap('SapSearchTable', PartNumber1,'NA') as PartNumber2,
SerialNumber,
ApplyMap('SapSearchTable', SerialNumber,'NA') as SerialNumber2,
[asset class no],
ApplyMap('SapSearchTable', [asset class no],'NA') as [asset class no1],
[asset class]

FROM
[Fixed Asset Register Recon Jan WIP For Thabo Data-Man.xlsm]
(ooxml, embedded labels, table is [Fixed Asset Register 1PART]);

store Found into C:\Users\BET\Downloads\10k;

Re: Mapping Require 2 column

prieper — Mon, 28 Jan 2019 11:25:39 GMT

This is exactly as the error-message says:
a mapping-table can have only two columns: value to read and value to write.
Naming of the column is not relevant.
Thus your script should read:
SapSearchTable:
Mapping LOAD
PMDescription,
AssetBarCode

FROM
[Fixed Asset Register Recon Jan WIP For Thabo Data-Man.xlsm]
(ooxml, embedded labels, table is InventoryList);

If you need another column for a further specification, you may have to consider either a nested mapping or to use a combined key.

HTH Peter

Re: Mapping Require 2 column

Vincent24 — Mon, 28 Jan 2019 11:30:50 GMT

Hi,

Thank you, I removed the other field however when I reload I'm getting an error that says:

Not sure what is wrong with my script.

Error Scrip

Field names must be unique within table
FoundTable:
LOAD PartNumber,
ApplyMap('SapSearchTable',PartNumber,'NA') as PartNumber1,
[SAP Asset Number],
Subnumber,
ApplyMap('SapSearchTable',Subnumber,'NA') as Subnumber2,
[Capitalized on],
ApplyMap('SapSearchTable', [Capitalized on],'NA') as [Capitalized on1],
Description,
ApplyMap('SapSearchTable', Description,'NA') as Description2,
Inventorynumber,
ApplyMap('SapSearchTable', Inventorynumber,'NA') as Inventorynumber2,
PartNumber1,
ApplyMap('SapSearchTable', PartNumber1,'NA') as PartNumber2,
SerialNumber,
ApplyMap('SapSearchTable', SerialNumber,'NA') as SerialNumber2,
[asset class no],
ApplyMap('SapSearchTable', [asset class no],'NA') as [asset class no1],
[asset class]

FROM
[Fixed Asset Register Recon Jan WIP For Thabo Data-Man.xlsm]
(ooxml, embedded labels, table is [Fixed Asset Register 1PART])

Re: Mapping Require 2 column

Vincent24 — Mon, 28 Jan 2019 11:40:14 GMT

Hi,

I'm new to qlik, what is a nested mapping or a combined key?

Thanks

Re: Mapping Require 2 column

nwest1965 — Mon, 28 Jan 2019 11:41:39 GMT

Partnumber1 is repeated in your load statement.

Re: Mapping Require 2 column

nwest1965 — Mon, 28 Jan 2019 11:51:00 GMT

Nested mapping - is where you use ApplyMap result to find a value in another Applymap function:

Standard Statement:

ApplyMap('MAP_TABLE_1', Field1, 'DEFAULT VALUE') AS FieldX

Field1 in this case is an existing value in the table, but what if we had to find the value of Field1 from a mapping table, using a different field in the table:

ApplyMap('MAP_TABLE_1', ApplyMap('MAP_TABLE_2', FieldABC, '0'), 'DEFAULT VALUE') AS FieldX

In the above example we are finding the key value to lookup on MAP TABLE 1 by using another lookup on MAP TABLE 2.

That is nested mapping.

A combined key is more often referred to as a Composite Key, it basically means combining several values into a single field that is replicated across tables and used to join the tables together. Without a composite key, QlikView will create $Syn values & tables, so the tidier way is for you to create the composite key yourself and retain control.

Re: Mapping Require 2 column

Vincent24 — Mon, 28 Jan 2019 11:57:38 GMT

Hi,

Please assist the the error, what I am doing wrong there:

Field names must be unique within table
FoundTable:
LOAD PartNumber,
[SAP Asset Number],
Subnumber,
[Capitalized on],
Description,
Inventorynumber,
PartNumber1,
SerialNumber,
[asset class no],
[asset class],
ApplyMap('SapSearchTable',PartNumber,'NA') as PartNumber1,
ApplyMap('SapSearchTable',Subnumber,'NA') as Subnumber2,
ApplyMap('SapSearchTable', [Capitalized on],'NA') as [Capitalized on1],
ApplyMap('SapSearchTable', Description,'NA') as Description2,
ApplyMap('SapSearchTable', Inventorynumber,'NA') as Inventorynumber2,
ApplyMap('SapSearchTable', PartNumber1,'NA') as PartNumber2,
ApplyMap('SapSearchTable', SerialNumber,'NA') as SerialNumber2,
ApplyMap('SapSearchTable', [asset class no],'NA') as [asset class no1]

FROM
[Fixed Asset Register Recon Jan WIP For Thabo Data-Man.xlsm]
(ooxml, embedded labels, table is [Fixed Asset Register 1PART])

Re: Mapping Require 2 column

nwest1965 — Mon, 28 Jan 2019 12:01:21 GMT

As I said above, PartNumber1 is duplicated in this load statement, and that is not allowed. So just change the second one to PartNumber3 (you have PartNumber2 already further down) or whatever else you want to call it.

FoundTable:
LOAD PartNumber,
[SAP Asset Number],
Subnumber,
[Capitalized on],
Description,
Inventorynumber,
PartNumber1,
SerialNumber,
[asset class no],
[asset class],
ApplyMap('SapSearchTable',PartNumber,'NA') as PartNumber1,
ApplyMap('SapSearchTable',Subnumber,'NA') as Subnumber2,
ApplyMap('SapSearchTable', [Capitalized on],'NA') as [Capitalized on1],
ApplyMap('SapSearchTable', Description,'NA') as Description2,
ApplyMap('SapSearchTable', Inventorynumber,'NA') as Inventorynumber2,
ApplyMap('SapSearchTable', PartNumber1,'NA') as PartNumber2,
ApplyMap('SapSearchTable', SerialNumber,'NA') as SerialNumber2,
ApplyMap('SapSearchTable', [asset class no],'NA') as [asset class no1]

FROM
[Fixed Asset Register Recon Jan WIP For Thabo Data-Man.xlsm]
(ooxml, embedded labels, table is [Fixed Asset Register 1PART])

Re: Mapping Require 2 column

Vincent24 — Mon, 28 Jan 2019 12:16:32 GMT

Thank you so so much, could't spot that problem

Re: Mapping Require 2 column

Vincent24 — Mon, 28 Jan 2019 12:22:10 GMT

The challenge I'm having now is that its not showing the fields from the first table:

Directory;
SapSearchTable:
Mapping LOAD PMDescription,
AssetBarCode

Only shows fields from the second table after reload.

Re: Mapping Require 2 column

nwest1965 — Mon, 28 Jan 2019 13:02:55 GMT

A mapping table is only a temporary table that exists only whilst the load script is being run. The purpose of a mapping table is to "map" field values from one source onto another. In your case for example the value of AssetBarCode from your mapping table is now contained in your primary table, and is contained within specific fields according to the value of PMDescription.

So the question is, why would you also want the source table itself?

It shouldn't be of any value to you.