How to pickup the first match using the ApplyMap?

[qlikviewforum]

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!

[tresesco]

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

[maxgro]

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

1

2

3

];

[hic]

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

[qlikviewforum]

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

[maxgro]

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

];

[qlikviewforum]

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!

[hic]

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

[qlikviewforum]

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

[qlikviewforum]

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