Closest number of given value

Help
1

closest

Here My target number is 107 .so i want to find next closest number that is 106 .Anyone can help me out of this guys?

2

Hi @aruna.p5,
Could you please give little more details and describe the process?

3

First of all …i need to get the input value from user and then check the given value with the input file like excel sheet.If the given value matches with any value in first column ,then get that particular row .If the given value does not exist in first column ,then find the next closest number of the given value…
.

123
123461Ă—890 7.61 KB

Here the given value is 111 ,then i need to fetch that particular row which contains 111 .or the given value is 115 ,i need to find the closest number of 115 and the expected output is either 112 or 118 in the given excel file .

4

Hmmm there is many ways to achieve this. First of all you can use compare method. Keep your excel data in DataTable and compare each value provided by user with values from the first column in DataTable. If something is matching then it will throw whole row to user. If nothing is matching then you could use iteration until some next value will be found.

Second thing I have in my mind is to use sorting method where all values from excel tabla would be a part of list and value provided by user will be added there. Finally all numbers would be sorted and value next to this provided by user would be used.

Like I said, there’s many ways.

5

@Pablito I tried few things.matching things i have done …i need to find closest number of the given value which is not exist in excel …can u please share some xaml file for logic ?

6

Hi @aruna.p5,
Can you please share the sample excel file i will build xaml file for u.

1 Like
7

(Note to bystanders: I’m aware this is an incredibly inefficient way to do this, and it will be bad for huge datatables).

The easiest thing you can do is:

  • Sort datatable values (by either ascending or descending, doesn’t matter)
  • Iterate through each row sequentially. Compare (substract) your input value with the column value. Store this delta in a variable, as well as the column value
  • Go to next row, compare again your input value with column value. Is your delta smaller than before? That means it’s your next “best candidate”. Store the relevant values
  • Do this for the entire table*. By the end, you’ll have the item that is closest to your input, as well as the row it was in (if you stored it)

Now, there are some optimisations here:

  • If your delta equals 0, that means the number is in the table. You can exit loop.
  • Because the table is sorted, if at one point your delta increases (when it was decreasing before) that means the closest item was the one before, so you can exit loop (hopefully saving some iterations)
1 Like
8

@aruna.p5 - You could try the “Filter Data Table” activity… Load your Excel data into data table, then use this activity to filter >= on sorted list, the your top row will be your closest match.

9

i dont think it will work @sagacity

10

closest.xlsx (10.2 KB)

Here i attached the excel file @Vashisht

11

Take a look at this post LinQ to find closest value, this will give you an idea around how you can find the closest number using LinQ

1 Like
12

@aruna.p5 Ok.

13

@aruna.p5,
Sorry for late reply. Here you have the simplest example:
numbers.zip (18.8 KB)
Those are data I’m providing:
image
The result will be closest number and value from column next to the number :wink:

14

sorry i couldnt open your .xaml file …ts shows some error regarding version @Pablito

15

Remove “project.json” file and open it from main.xaml

16

i have opened in the same way …ERROR
It shows like this @Pablito

17

Which version of Studio do you have?

18

not a latest one …i think 2018 th version @Pablito

19

That can be problem. Are you able to check it on other computer on Studio 19.7 CE?

21

@Pablito In your file ,it takes only greatest nearby number but i want closest number which is either lesser or greater than the given number