topic Re: How to pickup the first match using the ApplyMap? in QlikView

How to pickup the first match using the ApplyMap?

qlikviewforum — Fri, 23 May 2014 09:52:21 GMT

We have two dashboards, as it picks up multiple value from the mapping table we are serious data issue in the mapping table. So the data is inconsistent between both the data models. To have a consistent data in both the data model how can we always pick the first match in while using the applymap in both the data models so that data is consistent.(or) Can we use order by while storing the data into the mapping table?

Please suggest!

Re: How to pickup the first match using the ApplyMap?

tresB — Fri, 23 May 2014 10:01:58 GMT

I guess ApplyMap by default takes the first match. Have you experienced otherwise?

Re: How to pickup the first match using the ApplyMap?

maxgro — Fri, 23 May 2014 10:10:58 GMT

mee too I think that

regarding order maybe you can order the map table in this way

tmpmap:

LOAD * inline [

from,to

1,z

1,a

1,b

2,n

2,m

];

map:

mapping load * Resident tmpmap

order by from,to // comment or uncomment to see difference

;

DROP table tmpmap;

table:

load dim, ApplyMap('map', dim) as fieldmapped;

load * inline [

dim

];

Re: How to pickup the first match using the ApplyMap?

hic — Fri, 23 May 2014 10:11:34 GMT

ApplyMap does indeed always use the first instance found.

If you have two dashboards, and the ApplyMaps in the two return different values, then they must be differently sorted. So, yes, sorting by Order By (works with Load ...Resident) in both places will probably solve the problem.

HIC

Re: How to pickup the first match using the ApplyMap?

qlikviewforum — Fri, 23 May 2014 11:29:00 GMT

Thanks all for all your inputs!

To brief you more about the issue. We using the applymap based in the combination of the two values. For example if the value is Field1=A and value of Field2=B and in the mapping table if we have two or more value for this combination. 1st data model picks the 1st value(X) and 2nd data model picks the 2nd value(Y) and inconsistent data has been fetched in both data model.

Name Town City
A B X
A B Y

So we would like to know what changes are required to fetch the consistent data across the data model.

Hope the issue clear now. Let me know if you have any questions

Re: How to pickup the first match using the ApplyMap?

maxgro — Fri, 23 May 2014 11:46:37 GMT

perhaps I don't understand your question but it seems it works also with a combination of two fields

you always get the first (depends on order) city (01 for A B, 02 for C D)

tmpmap:

LOAD * inline [

name,town,city

A,B, city 99

A,B, city 01

C,D, city 69

C,D, city 02

];

map:

mapping load name & '-' & town, city Resident tmpmap

order by name,town,city

;

DROP table tmpmap;

table:

load dim1,dim2, ApplyMap('map', dim1 & '-' & dim2) as fieldmapped;

load * inline [

dim1,dim2

A,B

C,D

Z,Z

];

Re: How to pickup the first match using the ApplyMap?

qlikviewforum — Fri, 23 May 2014 15:52:15 GMT

Is there is anyway that it always cosider the first match and rest all deleted from mapping table? So that data is consistent across both the data models? Please confirm!

Re: How to pickup the first match using the ApplyMap?

hic — Fri, 23 May 2014 15:56:10 GMT

That is how it works. The first match is always used, and the others are neglected. The entire mapping table is deleted at the end of the script execution.

HIC

Re: How to pickup the first match using the ApplyMap?

qlikviewforum — Fri, 23 May 2014 16:20:38 GMT

In our case as we are getting inconsistent output in both the data models.

So you mean to say "Order By" in the mapping table will resolve this issue? Please confirm

Re: How to pickup the first match using the ApplyMap?

qlikviewforum — Sun, 25 May 2014 08:45:54 GMT

Hi,

I'm thinking of trying the below. I think below one should be working. Please let me know your suggestions on this.

RegionTable:

Mapping load

Load Name & "_" & Town,max(CityCode) as Code

From qvd

Group by Name,Town;

If above will not work then please let me know what can be done.

Thanks in advance

Re: How to pickup the first match using the ApplyMap?

qlikviewforum — Sun, 25 May 2014 10:46:54 GMT

Guys,

Above code will not work as the CountryCode will contains AlphaNumeric values so Min or Max wont work in this case... So was using MaxString/MinString in this case

Please suggest something if you have anything in mind

Re: How to pickup the first match using the ApplyMap?

maxgro — Sun, 25 May 2014 13:49:26 GMT

every code that will produce a mapping table

- ordered (see henric/tresesco/my answer)

- or with only one record by name and town (your answer using max/minstring group by, or with peek)

should works

Re: How to pickup the first match using the ApplyMap?

preminqlik — Sun, 25 May 2014 15:15:34 GMT

hi there,

in fact mapping always chooses first element, if you want to try with another mapping script here it goes : Using firstsortedvalue

MAP_DATA:

LOAD *,Rowno() as Rowno INLINE [

Name, Town, City

N1, T1, C1

N1, T1, C2

N1, T1, C3

N2, T1, C4

N2, T1, C5

N2, T1, C6

];

CITY_MAP:

mapping load Name&'-'&Town as MAP_CITY_KEY,

City;

Load

Name,

Town,

FirstSortedValue(City,Rowno) as City

Resident MAP_DATA group by Name,Town;

DROP table MAP_DATA;

ORGINAL_TABLE:

LOAD *,

ApplyMap('CITY_MAP',Name&'-'&Town,'No Data') as City;

LOAD * INLINE [

Name, Town, Data, Bla

N1, T1, DATA1, BLA

N2, T1, DATA2, BLA BLA

N3,T1,DATA3,BLA BLA BLA

];

Re: How to pickup the first match using the ApplyMap?

qlikviewforum — Sun, 25 May 2014 18:55:06 GMT

Thanks Massimo and Prem.

Will try your options and will update you as soon as possible

Re: How to pickup the first match using the ApplyMap?

Anonymous — Thu, 08 Mar 2018 07:03:43 GMT

Hi Prem,

I'm trying to modify this code, by allowing it to loop through the mapping list. (see attached)

With the same key, is there any way I could get the 2nd and 3rd matches using a while/for loop?

Kindly suggest any other better methods.

Thanks in advance!