Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
kishorj1982
Contributor II

Problem with applymap

Hi All,

 

We have a number of mappings and apply map function wrt one table and these mappings used in a number of applications.

To reduce loading time I have created a centralise mapping file and used these calculated mappings in a different application.

 

But I found that the result is not matching.

 

Can you please suggest what could be the best way to avoid repetitive mapping calculation time for the number of applications.

 

Thanks

Labels (2)
10 Replies
aveeeeeee7en
Valued Contributor III

Re: Problem with applymap

Hi Kishorj,

 

If possible than please share the sample Qlikview Application.

Regards,

Av7eN

Partner
Partner

Re: Problem with applymap

Make sure you got distinct key values in your mapping table. If you do not have distinct key values you might get different result depending on which order you load the rows in your master table. 

The first map table will not return the same values as the second.

The first:

 

MyMapTable:
MAPPING LOAD * [ MapKey, MapValue A, Ali B, Betty C, Cecil A, Anna B, Billy C, Conny ];

 The second:

 

MyMapTable:
MAPPING LOAD * [
MapKey, MapValue
A, Anna
B, Billy
C, Conny
A, Ali
B, Betty
C, Cecil ];
#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.
shiveshsingh
Honored Contributor

Re: Problem with applymap

If you have more than 1 mapping values in your mapping table, then only first one will be picked in your mapping load.

kishorj1982
Contributor II

Re: Problem with applymap

s this mean that all values in mapping load must be distinct and in apply map is it need to use distinct clauses on the primary key in the tablle

Partner
Partner

Re: Problem with applymap

You don't have to, but as I and @shiveshsingh said it will map the value of the first loaded keyvalue of the map table and "ignore" all later duplicate key values.

However it could be an good idea to load each key value only once to avoid confusions or diffrent mapping output when used with different load order.

If you are 100% certain that you only have distinct values in your table then you will get the same result from a left join as from using applymap().
#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.
MVP & Luminary
MVP & Luminary

Re: Problem with applymap

In addition to the other statements a mapping-table could be (variously) sorted with an order by statement and your lookup value could be extended with an appropriate counter, like: value & '|' & counter-number and then an applymap() could grab the n-th value. But there are not many usecases in which such advanced approaches are really useful - therefore keep it simple and ensure that only distinct values are within your mappings.

- Marcus

kishorj1982
Contributor II

Re: Problem with applymap

Hi All,

 

PFA.

 

I have created a mapping file for avoiding duplicate mappings from multiple applications.

I am creating transformed QVd for this and wherever mapping columns are required I am fetching these columns from created transformed QVd.

The purpose is to add all mappings related to SalesOrder.qvd to SalesOrderMappings.QVW file and use transformed

SalesOrderMappings.qvd file in all applications.

 

 

but looks like values does not match.

Can you please suggest where I need to test this application.

 

Thanks

 

 

Partner
Partner

Re: Problem with applymap

Which version are you running the application on. Previous versions of
QlikView had problem creating mapping tables from QVD files when loaded
with optimized load.

Try losing your mapping table using a where clause 1=1, it will break your
optimized load.

MapTable:
MAPPING LOAD
KeyField, ValueField
FROM
MapFile.qvd (QVD)
WHERE
1=1;

BR
Vegar
#OnVacation #BetweenJobs.
Please ekskuse my Norglish and Swenglish typos.
kishorj1982
Contributor II

Re: Problem with applymap

Hi Vegar,

 

Thanks for your valuable inputs. We are using Qlikview 12 but I do not know. Anyway, I will make changes suggested by you.

If there is already where condition I am using and statement 

 

[Handling Unit Creation Date Map]:
MAPPING LOAD [Sales Document Item Key],
Date (Min ([Handling Unit Creation Date])) as [Handling Unit Creation Date]
RESIDENT temp
GROUP BY [Sales Document Item Key] and 1=1;

 

 

Please correct me if I am wrong

 

Thanks