Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Vincent24
Contributor II
Contributor II

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
prieper
Master II
Master II

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

View solution in original post

10 Replies
prieper
Master II
Master II

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

View solution in original post

Vincent24
Contributor II
Contributor II
Author

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
Contributor II
Contributor II
Author

Hi, 

 

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

 

Thanks

nwest1965
Contributor III
Contributor III

Partnumber1 is repeated in your load statement.

nwest1965
Contributor III
Contributor III

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
Contributor II
Contributor II
Author

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

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
Contributor II
Contributor II
Author

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

Vincent24
Contributor II
Contributor II
Author

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.