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.
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
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 .
@sandeep13 - No worries bro… You enjoy… while we take care of it
@md.ahtesham
I think you can read the two sheets into two data tables and then Join them together with the matching columns.
Read Range activity to read the input sheet
Read Range activity to read the template sheet
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…
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. c# - Closest value in DataTable - Stack Overflow ,but I don’t know how can I convert so that it can be used in uipath.
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…
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.
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?
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.
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.
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.
Anil_G
arjunshenoy
Yoichi
gibl.rpa
Hazem_Saleh
adishjain
yikwen.goo
trajkoskisara
Parvathy
DanRagh