Qlik Community

Ask a Question

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Applymap() not fetching all records

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,

9 Replies

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)

Champion III
Champion III

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.

Not applicable

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,

Not applicable

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 ?

Champion III
Champion III

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.

Not applicable

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.

Champion III
Champion III

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

Not applicable

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.