Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

petergassert
New Contributor

ApplyMap / Lookup Closest Value

Hi,

I am looking for a function similar to VLookup with the Setting to return the value to the closest number, as the Input Parameter is not given at all.

ApplyMap or Lookup just searches for the same value.

In my case i got a number with 2 decimal places. For this value i need to return the closest relevant Parameter.

The challange is, that the mapping needs to be done with a number that got 3 decimal places.

Could not find a function that returns the closest value to my mapping field.

Would be great if you have any idears.

Thanks,

Peter

7 Replies
shraddha_g
Honored Contributor III

Re: ApplyMap / Lookup Closest Value

Share Sample data.

Where as you can do number formatting of field however you want and then do applymap.

For ex. You can write your Mapping load as

Mapping Load

Num(Field,'#,##0.000') as Field,  // You can change this according to number of decimal you require.

FieldtoMap

From ....;

Else same thing you can do with Second table where you are applying apply map.

Map_Table:

Mapping Load

Field,

FieldtoMap

From ...;

Table2:

Load

Applymap('Map_Table',num(Field,'#,##0.00')) as Newfield

From ..

petergassert
New Contributor

Re: ApplyMap / Lookup Closest Value

Hi Shraddha Gajare,

thanks for the reply.

I added an Excel file with sample data. Do i have to publish his as well?

The number formatting i tryed already as well, but sadly then the first value, depending on the load will be returned by lookup.

My aim is to pick the Closes number of the 3 decimal to the 2 decimal placed number.

Cheers,

Peter

shraddha_g
Honored Contributor III

Re: ApplyMap / Lookup Closest Value

also share data where you want do applymap

MVP
MVP

Re: ApplyMap / Lookup Closest Value

Round the value you are looking up in the applymap call, like this

ApplyMap('Mapping_Table', Round(Source_Field, 0.01))


This assumes that all the rounded-to-2-decimal source values exist in the mapping file. If the mapping does not include all the needed 2 decimal numbers, then you may have to look to interval matching rather than a mapping lookup,

petergassert
New Contributor

Re: ApplyMap / Lookup Closest Value

Thanks for the reply.

But what do you do, as the Source field is already set with 2 decimal places.

In My case the Mapping Field is 3 Didget and with rounding this, the values will multiplicate.

Example: Source Field 0.04

Map:

Mapping LOAD * INLINE [

  "Distance to Map", "Return Parameter A"

0.049, -305.875407

0.048, -100.6281872

0.047, -875.8585018

0.047, -512.6253199

0.046, -268.3870513

0.045, -341.372512

0.044, -638.106656

0.044, -938.6484605

0.043, -623.0204121

0.042, -537.2067727

0.041, -863.4453454

0.038, -341.372512

;

ApplyMap('Map', 0.04)

Which value will Applymap return as the full Value does not exist.

My wish would be the return the Closes number to 0.04 that would be in my case 0.041.

shivanandk
Contributor II

Re: ApplyMap / Lookup Closest Value

Also you can define range value and use intervalmatch function. See the detail in the help with example.

IntervalMatch ‒ Qlik Sense

petergassert
New Contributor

Re: ApplyMap / Lookup Closest Value

Hi Shlvanand Kamath,

thanks for your reply.

The idea wit the interval match i had as well.

But after the interval match, what would your suggestion:

- with multiple results within the range?

  How do I get the closes value?

  In this case i need to add after the intervalmatch a extra column with the Minimum difference.

  Afterwards i need to match the values again. Lot of rubbish will be generated that will be thrown away afterwards.

- with no result within the range? Try and error with the respective rangesize?

Isn't there an easier way?