Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

ApplyMap wrong results

Hi,

I use 2 maps to match the data I've got and the table below is the result.

Some ID2s match 2 different ID1s and therefore should match 2 different IDs, which works fine. However, the ID2 data (downloads) is assigned only to the first ID while the second one gets a value of 0, and I need both IDs to get the same value.

ID(4) = 4 and ID(5) = 4 instead of ID(4) = 4 and ID(5) = 0

11.png

I would really appreciate any help!

Ivelin

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: ApplyMap wrong results

Try using JOINs.

Please check attached.

8 Replies

Re: ApplyMap wrong results

Applymap works fine. You only have three ID2 values that have two entries in your mapping table. That's an error because it's impossible to determine which of the two values should be applied. Applymap uses only the first and ignores all others. You may not agree with that logic, but that's the way it works I'm afraid. You'll have to think up some way to make the ID2 values unique.


talk is cheap, supply exceeds demand
Not applicable

Re: ApplyMap wrong results

That's really bad. There is no way I can make them unique.

Do you think there is another way around it?

Gabriel
Valued Contributor II

Re: ApplyMap wrong results

Hi Ivelin,

I think in this case Map (ApplyMap) is wrong function to use.

Just think of ApplyMap as Vlookup in excel, VLookup a id and return first found value (meaning, if you have duplicate Salesperson and different SalesValue, Vlookup will pick up the first value and ignore the sceond). By default DISTINCT is applied.

I will think of another function that might help or you can thik of another function as well

Regards,

Gabriel

MVP
MVP

Re: ApplyMap wrong results

Try using JOINs.

Please check attached.

Gabriel
Valued Contributor II

Re: ApplyMap wrong results

Hi,

Can I ask you a question?

Is the data coming from 2 tables?

If yes then, I will suggest using INTERVALMATCH() function.

This function accept and display duplicate.

Regards,

Gabriel

Not applicable

Re: ApplyMap wrong results

Hi Gabriel,

thanks for the explanation above! Now I understand why Map doesn't work the way I want it.

As for the data, yes it comes from 2 (actually 3) sources. It looks the same as in 'data1' and 'data2' sheets in the excel file I've attached.

Not applicable

Re: ApplyMap wrong results

Thanks swuelh!

I will try your solution out right away. I was thinking about JOIN as well but I remember reading some posts that don't advice on using it, I don't really know why. Are there any limitations or drawbacks in using it?

Gabriel
Valued Contributor II

Re: ApplyMap wrong results

Hi,

Thanks for comment given above. Please mark the answer as Helpful.

Regards

Community Browser