topic Re: Applymap() not fetching all records in QlikView

Applymap() not fetching all records

Tue, 17 Mar 2015 18:49:10 GMT

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,

Re: Applymap() not fetching all records

Anonymous — Tue, 17 Mar 2015 18:56:05 GMT

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)

Re: Applymap() not fetching all records

Anonymous — Tue, 17 Mar 2015 19:00:25 GMT

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.

Re: Applymap() not fetching all records

Tue, 17 Mar 2015 19:02:15 GMT

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,

Re: Applymap() not fetching all records

Tue, 17 Mar 2015 19:13:59 GMT

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

Re: Applymap() not fetching all records

Anonymous — Tue, 17 Mar 2015 19:29:58 GMT

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

Re: Applymap() not fetching all records

Anonymous — Tue, 17 Mar 2015 19:30:43 GMT

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.

Re: Applymap() not fetching all records

Tue, 17 Mar 2015 19:55:27 GMT

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.

Re: Applymap() not fetching all records

Anonymous — Tue, 17 Mar 2015 20:17:03 GMT

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

Re: Applymap() not fetching all records

Tue, 17 Mar 2015 20:33:08 GMT

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.