Map/Apply Map error

[#1]

Hi,

I am trying to map one field from one table and add this field to another table by using ApplyMap.

The problem is that I have to construct a new Key using two fields from the table to be able to link the tables.

Not sure if this is possible of how to do it, but I have given it a try (see code below).

I get the following error message when I do the load for the MAP1: Field not found <t_cwar>

Anyone how knows how to do this? Thanks in advance.

Script:

// *******Inventory Value**********
ItemInventoryValue:
LOAD
"t_cwar"&'-'&"t_item" as WarehouseItem,
"t_mauc_1" as MAUC1;
SQL SELECT *
FROM erplndb.dbo.twhwmd217100;

Map1:
MAPPING LOAD "t_cwar"&'-'&"t_item" as WarehouseItem, MAUC1 Resident ItemInventoryValue;

// *******Inventory Figures*******
ItemInventory:
LOAD "t_cwar" as Warehouse,
"t_item" as ItemNo,
"t_cwar"&'-'&"t_item" as WarehouseItem,
"t_qhnd" as Inv_OnHand,
"t_qhnd"*ApplyMap('Map1',WarehouseItem,0) as OH_Inv_Value,

etc.

[hector]

Hi, you need to make this change

// *******Inventory Value**********
ItemInventoryValue:
LOAD
 "t_cwar"&'-'&"t_item" as WarehouseItem,
 "t_mauc_1" as MAUC1;
SQL SELECT *
FROM erplndb.dbo.twhwmd217100;

Map1:
MAPPING LOAD WarehouseItem as WarehouseItem_MAP, MAUC1_MAP Resident ItemInventoryValue;
//The _MAP sufix it's to avoid the autoconcatenation with the 1st table

// *******Inventory Figures*******
ItemInventory:
LOAD "t_cwar" as Warehouse,
 "t_item" as ItemNo,
 "t_cwar"&'-'&"t_item" as WarehouseItem,
 "t_qhnd" as Inv_OnHand,
 "t_qhnd"*ApplyMap('Map1',WarehouseItem,0) as OH_Inv_Value,

but, i'm not following the reason you are loading the same table twice

Rgds

[#3]

Hi,

one thing is load only the field "WarehouseItem". It seems to be an Copy/Past error.

Map1:
MAPPING LOAD

WarehouseItem,

MAUC1

Resident ItemInventoryValue;

Where are your ApplyMap line in the script?

Rainer

[#4]

If I try MAPPING LOAD WarehouseItem as you suggest I get the error message: Warehouse item cannot be found.

[#5]

The apply map is in the last line in table number two:

"t_qhnd"*ApplyMap('Map1',WarehouseItem,0) as OH_Inv_Value,

I have two tables:
1. Inventory value
2. Inventory quantity

I need to combine them into one and I have to use the combination of Warehouse and Item to get the right key.

[hector · Accepted solution]

Hi,

There is another problem, in the applymap you are using an alias instead of the original fields

"t_qhnd"*ApplyMap('Map1',WarehouseItem,0) as OH_Inv_Value,

replace with

"t_qhnd"*ApplyMap('Map1',t_cwar&'-'&t_item,0) as OH_Inv_Value,

The solution suggested works, but i'm not sure if the mapping table will auto-concatenate with the 1st one, because both share the same fields

rgds

[#7]

Thank you so much! This did it!