Mapping Require 2 column

[Vincent24]

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;

[prieper · Accepted solution]

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

[Vincent24]

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])

[Vincent24]

Hi, 

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

Thanks

[nwest1965]

Partnumber1 is repeated in your load statement.

[nwest1965]

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.

[Vincent24]

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])

[nwest1965]

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])

[Vincent24]

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

[Vincent24]

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.