How to find the close value in data Row

Hi Team ,

I have a task where i have to update the main template based on closer value range.
Input:

Template on which i need to update:

And Output should should look like below:

In Pic 1 - Its only the amount which is varying from customer but on the Result Template (Pic -2) there is Extended Price column which gives idea of Price range .
Eg- Amount ( 8500.6) in Pic 1 is close to 8400.4 (Extended Price) column in Pic 2 , so we need to update corresponding Amount Column in Pic 2 with 8500.6 value.

Can you please help me on this… @Palaniyappan, @HareeshMR, @sandeep13, @pattyricarte
If something is not clear , then please revert.

Sample Attachment: Sample.xlsx (11.3 KB)

Ahtesham

2 Likes

Just to give you the brief idea on this, you have the same customer ID repeating so that, how can you identify the value in both the sheets? Or, Are you sure if both the sheets have data in the same rows everytime in the same row? I mean, the first row in sheet1 matches with the first row in sheet2 as well?

One more question, you are not even pasting the maximum value comparing both the sheets, I don’t think this can be automated, sorry if I’m wrong

2 Likes

Q. Are you sure if both the sheets have data in the same rows everytime in the same row? I mean, the first row in sheet1 matches with the first row in sheet2 as well?
Ans : It can be anywhere in Sheet2 .

We can take range as (plus - minus) 1000

Ahtesham

1 Like

Not sure about it @md.ahtesham. Sorry

1 Like

@md.ahtesham I am out of town with less network connection. Sorry for this…
@lakshman @Lahiru.Fernando @Jan_Brian_Despi guys please help

5 Likes

Hello @md.ahtesham

@sandeep13 - No worries bro… You enjoy… while we take care of it :slight_smile:

@md.ahtesham
I think you can read the two sheets into two data tables and then Join them together with the matching columns.

  1. Read Range activity to read the input sheet
  2. Read Range activity to read the template sheet
  3. Now use a Join Data Table activity and join the two datatables using these columns
    • Customer Number
    • Type
    • Renewal Date
    • Product Name

This will join the two datasets and give you the required output.
4. Now do a write range to get the output… In here, if you get additional columns, Just filter out the additional columns or delete them using the respective activities…

5 Likes

Thanks for your reply @Lahiru.Fernando,

I don’t think join operation will work.
If you closely look on template sheet there is a column - Extended price ,indication as in which customer should be mapped for similar customer number.

I found one article which can maybe help to find close value based on which I can map each customer.
https://stackoverflow.com/questions/38502554/closest-value-in-datatable ,but I don’t know how can I convert so that it can be used in uipath.

Ahtesham

2 Likes

Hey @md.ahtesham

On I see. Sorry for not understanding it properly the first time.

You can do this… see in the example, it looks for the ID column for closest value. This is important to locate the exact row easily

So first add a new data table column then, use a for each row loop, and add a unique ID for each row using a incrementing number variable.

Then for the filtering in the example in the link you shared, you can use the option give in the first answer there. Use it and locate the closest number for each value. All this should happen inside a for each row because you have to look for the closest for each variable.

Then, based on the ID it returns, you can then do a join and get the output. Here, you will get a datatable with one row. Then you will need to add it to a final data table row by row.

Make sense or looks like Greek? I could have drawn and shown it, but right now I’m not in front of any paper…

2 Likes

Hey,
Sorry if I misunderstand, but I think it would just be so simple to loop over your Template which you need to fill out, match with the rows that equal the customer number, sort it by the difference using both values, then fill in the column with the input amount that has lowest difference which would be the first row when sorted.

So let’s assume we have two tables, inputData and templateData…
-We first use a ForEach activity on the template so we can fill each row out
-Then assign all the rows that match the customer number
-Check that a match was found, and then sort the matched rows by the difference
-Which gives you the smallest difference to fill into the template row

For each rowTemplate In templateData
    Assign: inputMatches = inputData.AsEnumerable.Where(Function(r) r("Customer Number").ToString.Trim.PadLeft(15,"0"c) = rowTemplate("Customer Number").ToString.Trim.PadLeft(15,"0"c) ).ToArray
   IF activity: inputMatches.Count > 0
       Assign: inputMatches = inputMatches.OrderBy(Function(r) Math.Abs( If(IsNumeric(r("Amount").ToString.Trim), Convert.ToDouble(r("Amount").ToString.Trim), 0) - If(IsNumeric(rowTemplate("Extended Price").ToString.Trim), Convert.ToDouble(rowTemplate("Extended Price").ToString.Trim), 0) ) ).ToArray
       Assign: rowTemplate("Amount") = inputMatches(0)("Amount").ToString.Trim

-The last assign should end up placing the lowest difference from the matched rows into the Amount column for each template row in the loop

Hopefully, I did that right. But anyway, that’s about what I would end up doing in order to match up the amount that is closest to the Extended Price amount.

Also, I put in some additional logic when taking the difference, so if an amount is not a number it will still calculate the difference and not error out.

Regards.

5 Likes

Sorry to ask you this @ClaytonM, As we have the same customer number repeated in the sheet, and if we loop, there is a chance to get the wrong value if order is not the same. Could you please let me know is there any chance to do pick the correct values in that scenario?

2 Likes

Hi @HareeshMR
The idea (and I just thought of it quick) was to loop only the template which you are filling out. By doing so, you would not repeat the same row again as to only fill in each row once. Then, using each row in that loop, you would look for all the rows that match the customer number - this will give you multiple rows with different Amounts. You would then simply sort those rows by the difference, which will place the Amount with the smallest difference on the top. Thus, giving you the Amount with the closest value to the Extended Price.

Does that make more sense?

So it’s not dependent on the order of the rows or anything like that. It simply would fill in each template row with the amount having the smallest difference.

Regards.

4 Likes

Got it Sir @ClaytonM Thank you for the clarification :slight_smile:. That was really good way which I never thought of.

4 Likes

How to resolve this kind of error?

Sorry , i know this is very silly question.

Ahtesham

Ignore it now … I have solved it… Its actually Math.Abs and i was Thinking that its Match.Abs
Thanks @ClaytonM

2 Likes

Honestly speaking , i looks Greek and Latin to me :slight_smile:

Ahtesham

2 Likes

@ClaytonM I am trying to get the index number of template excel so that i can update the value on excel.
image

Everytime i am getting Index as 1.

Any Suggestion where i am doing wrong… @HareeshMR

Ahtesham

2 Likes

That is correct. Sorry about my typo

1 Like

You don’t need the index.
Your Assign activity which assigns row(“Amount”) = inputMatches(0)(“Amount”).ToString puts it into the data table. All you need to do is use an Excel Write Range either inside the ForEach or outside the ForEach.

If you place it inside, it will update the Excel after each row, or if you put it outside it will update it after all the values have been updated. Updating it one row at a time will be slower, but is typically recommended if you are doing various actions per row, and is not recommended if all you are doing is updating the Excel file.

Regards.

2 Likes

Hi @ClaytonM,
Sorry to disturb you, i spent almost entire day on this. but didn’t get success.
I have attached .xaml and excel file. can you please help with updating the result in Output Sheet.

Near Value.zip (21.7 KB)

1 Like

Thanks @ClaytonM … Its working.

Ahtesham

4 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.