7 Replies Latest reply: Sep 8, 2017 9:32 AM by Peter Gassert RSS

    ApplyMap / Lookup Closest Value

    Peter Gassert

      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

        • Re: ApplyMap / Lookup Closest Value
          Shraddha Gajare

          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 ..

            • Re: ApplyMap / Lookup Closest Value
              Peter Gassert

              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

                • Re: ApplyMap / Lookup Closest Value
                  Shraddha Gajare

                  also share data where you want do applymap

                  • Re: ApplyMap / Lookup Closest Value
                    Jonathan Dienst

                    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,

                      • Re: ApplyMap / Lookup Closest Value
                        Peter Gassert

                        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.