Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Applymap() not fetching all records

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)

mov
Esteemed Contributor III

Re: Applymap() not fetching all records

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

Re: Applymap() not fetching all records

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

Re: Applymap() not fetching all records

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 concernSmiley Sad

Re: Applymap() not fetching all records

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

mov
Esteemed Contributor III

Re: Applymap() not fetching all records

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

Re: Applymap() not fetching all records

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.

mov
Esteemed Contributor III

Re: Applymap() not fetching all records

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

Not applicable

Re: Applymap() not fetching all records

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.

Community Browser