Applymap() not fetching all records

[#1]

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,

[Anonymous]

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)

[Anonymous]

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.

[#4]

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,

[#5]

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:(

[Anonymous]

Are you hoping ApplyMap() will return more than one value ?

[Anonymous]

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.

[#8]

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.

[Anonymous]

That means this is what you have in the second map... 

[#10]

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.