Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have mapped one of the table using applymap(), but i'm getting two records out of the field and one record is getting dropped.
MonthYear_Table:
Mapping Load
Product_Desc,
Month_Year
From[ ..Tablename.qvd](qvd) where country<>'ABC';
Mapping Load
Product_Desc,
Prod_Category_ID
From[ ..Tablename.qvd](qvd) where country<>'ABC';
Table3:
Load Plant,
%PP_Key
Month_Year,
Prod_Category_ID,
Prod_Name,
Prod_Goal
-
-
FROM [....Tablename.qvd](qvd)
Table1:
Load
Plant&Applymap('MonthYear_Table',Product_Desc)&Applymap('MonthYear_Table',Product_Desc) as %PP_Key,
Applymap('MonthYear_Table',Product_Desc,'0') as Month_Year,
Applymap('MonthYear_Table',Product_Desc,'0') as Prod_Category_ID
-
-
-
FROM
[..Table.qvd](qvd)
Thanks,
Try it like this with bits in red.
MonthYear_Table:
Mapping Load
Product_Desc,
Month_Year
From[ ..Tablename.qvd](qvd) where country<>'ABC';
MapProd_Category_ID
Mapping Load
Product_Desc,
Prod_Category_ID
From[ ..Tablename.qvd](qvd) where country<>'ABC';
Table3:
Load Plant,
%PP_Key
Month_Year,
Prod_Category_ID,
Prod_Name,
Prod_Goal
-
-
FROM [....Tablename.qvd](qvd)
Table1:
Load
Plant&Applymap('MonthYear_Table',Product_Desc)&Applymap('MapProd_Category_ID',Product_Desc) as %PP_Key,
Applymap('MonthYear_Table',Product_Desc,'0') as Month_Year,
Applymap('MapProd_Category_ID',Product_Desc,'0') as Prod_Category_ID
-
-
-
FROM
[..Table.qvd](qvd)
You don't have name for you second mapping table, and you use the first mapping table in the applymap when you have to use the second.
MonthYear_Table:
Mapping Load distinct
Product_Desc,
Month_Year
From[ ..Tablename.qvd](qvd) where country<>'ABC';
CategoryMap:
Mapping Load distinct
Product_Desc,
Prod_Category_ID
From[ ..Tablename.qvd](qvd) where country<>'ABC';
...
Table1:
Load
Plant&Applymap('MonthYear_Table',Product_Desc)&Applymap('CategoryMap',Product_Desc) as %PP_Key,
Applymap('MonthYear_Table',Product_Desc,'0') as Month_Year,
Applymap('CategoryMap',Product_Desc,'0') as Prod_Category_ID
I don't now what you mean "missing records", but this is the fix you have to make.
That was just a type mistake. I gave some the table name. I'm getting only two records when i filter out with month.
I'm expecting three records from the mapped field.
Thanks,
Hi Michael,
I'm expecting three Prod_Category_ID after the mapping load. But i'm seeing two of them.
One ID is dropped out.
That's my concern:(
Are you hoping ApplyMap() will return more than one value ?
I can't tell how the applymap can remove records. I need to investigate what exactly is in the mapping tables and in the data.
Advice, that is maybe not relevant here. Always use distinct (except inline load) when create maps. I've been through a few problems before using it.
Actually the result i'm getting is like two Product names are mapped to one ID that looks very weird to me though.
Thanks for the reply.
That means this is what you have in the second map...
Yes Bill,
It should return three values from the mapped table actually. But it's giving two values.
One ID is mapped to two Product Names.That looks so strange to me even.