topic Problem with applymap in QlikView

Problem with applymap

kishorj1982 — Sat, 16 Nov 2024 04:10:44 GMT

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

Re: Problem with applymap

aveeeeeee7en — Thu, 14 Mar 2019 08:31:46 GMT

Hi Kishorj,

If possible than please share the sample Qlikview Application.

Regards,

Av7eN

Re: Problem with applymap

Vegar — Thu, 14 Mar 2019 09:02:43 GMT

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
];

Re: Problem with applymap

shiveshsingh — Thu, 14 Mar 2019 10:31:09 GMT

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

Re: Problem with applymap

kishorj1982 — Thu, 14 Mar 2019 11:40:19 GMT

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

Re: Problem with applymap

Vegar — Thu, 14 Mar 2019 12:28:57 GMT

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().

Re: Problem with applymap

marcus_sommer — Thu, 14 Mar 2019 14:24:22 GMT

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

Re: Problem with applymap

kishorj1982 — Thu, 14 Mar 2019 18:01:08 GMT

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

Re: Problem with applymap

Vegar — Thu, 14 Mar 2019 18:08:40 GMT

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

Re: Problem with applymap

kishorj1982 — Fri, 15 Mar 2019 05:31:52 GMT

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

Re: Problem with applymap

kishorj1982 — Tue, 19 Mar 2019 17:27:00 GMT

Hi,

I am using apply map in where the condition as per below.

[Sales Order Purchased]:
LOAD DISTINCT [Sales Document Item Key] as [Sales Document Item Key Purchased]
FROM
[SalesOrder.qvd]
(qvd)
WHERE Match ([%Sales doc. type_DOC_TYPE], 'ZQT', 'ZQH') = 0
AND Exists ([Sales Document Item Key]) //AND NOT Exists ([%Sales Document TECO Complete Item], [Sales Document Item Key])
AND ApplyMap ('PO Material Map', [Sales Document Item Key], 'N/A') = %Material_MATERIAL;

If I can use the calculated column instead of ApplyMap ('PO Material Map', [Sales Document Item Key], 'N/A') the result does not match.

Please suggest.

Thanks