Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Vincent24
New Contributor

Mapping Require 2 column

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;

 

 

1 Solution

Accepted Solutions
Highlighted
prieper
Honored Contributor II

Re: Mapping Require 2 column

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
10 Replies
Highlighted
prieper
Honored Contributor II

Re: Mapping Require 2 column

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
New Contributor

Re: Mapping Require 2 column

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
New Contributor

Re: Mapping Require 2 column

Hi, 

 

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

 

Thanks

nwest1965
New Contributor III

Re: Mapping Require 2 column

Partnumber1 is repeated in your load statement.

nwest1965
New Contributor III

Re: Mapping Require 2 column

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
New Contributor

Re: Mapping Require 2 column

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
New Contributor III

Re: Mapping Require 2 column

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
New Contributor

Re: Mapping Require 2 column

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

Vincent24
New Contributor

Re: Mapping Require 2 column

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.