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.
