Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ..
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
also share data where you want do applymap
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,
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.
Also you can define range value and use intervalmatch function. See the detail in the help with example.
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?