Hello everyone,
Can you help me in scenario.
If the sum of any two number is 0 ( number should be exactly same after decimal )
then update status as “Match” else “Open Item”.
| Amount | Status |
|---|---|
| -45646.9 | Open Item |
| 45646.85 | Open Item |
| 121 | Match |
| 100 | Open Item |
| 0.01 | Open Item |
| -0.11 | Open Item |
| -121 | Match |
| -13.46 | Open Item |
| 12.74 | Open Item |
| 45820.2 | Open Item |
| 1093.3 | Match |
| -45820.22 | Open Item |
| -1093.3 | Match |
Try this in invoke code
Send dt as in/out argument and dt1 as in argument and give same datatable to both
Dt.AsEnumerable.ToList.ForEach(sub(r) r("Status") = If(dt1.AsEnumerable.Where(function(x) cdbl(x(0).ToString) = 0-Cdbl(r(0).ToString)).Count=1,"Matched","Open item"))
Cheers
@Anil_G not getting the correct output status value.
you can test the same scenario on this data.
| Accounted Amount | |
| -258 | |
| -206 | |
| 2900.46 | |
| -17.07 | |
| -580.85 | |
| -25.8 | |
| -1999.5 | |
| 258 | |
| 25.8 | |
| -25.8 | |
| 16858.2 | |
| 3030.41 | |
| -21648.3 | |
| -8866.12 | |
| -25.8 | |
| -258 | |
| -108.88 | |
| -45724.2 | |
| -1031.18 | |
| -12612.2 | |
| 25.8 | |
| -258 | |
| 9531.08 | |
| -170.67 | |
| -25.8 | |
| -25.8 | |
| 258 | |
| 25.8 | |
| -945.91 | |
| 258 | |
| -25.8 | |
| -25.8 | |
| -1472.56 | |
| -25.8 | |
| 1472.56 | |
| -664.96 | |
| 4528.82 | |
| -258 | |
| 25.8 | |
| -202.36 | |
| 258 | |
| -258 | |
| 258 | |
| -4528.82 | |
| 45724.2 | |
| -25.8 | |
| 206 | |
| -258 | |
| -258 | |
| 258 | |
| 23271.95 | |
| 13558.09 | |
| 1560.53 | |
| 258 | |
| 25.8 | |
| -25.8 | |
| -258 | |
| 25.8 | |
| -1451.65 | |
| -3030.41 | |
| -258 | |
| 258 | |
| 1999.5 | |
| 258 | |
| -258 | |
| -258 | |
| 7042.26 | |
| -25.8 | |
| 25.8 | |
| 25.8 | |
| 25.8 | |
| 25.8 | |
| 258 | |
| 580.85 | |
| 25.8 | |
| -25.8 | |
| -15682.1 | |
| 187.74 | |
| -258 | |
| 258 | |
| 14780.24 | |
| 25.8 | |
| -258 | |
| -25.8 | |
| -7042.26 | |
| 25.8 | |
| 258 | |
| 258 | |
| -1623.62 | |
| -13749.1 | |
| -1176.15 | |
| -2698.1 |
@Anil_G
Sum.xlsx (24.7 KB)
Still not getting the correct output , i have attached the matched value in this excel file.
If you look at 11.32 is repeated 22 times and only one value is negative but it showed all of the value as matched. same goes for 22.64 it is repeated around 208 times but it has 188 negative value and 20 positive values but it shows them as matched
So you want only one set to be matched or all?
Because as per initial requirement you were saying any 2 numbers sum to be zero
Cheers
@Anil_G So what my requirement is I want to check the sum of any 2 number is 0 with opposite sign in the Accounted Amount Column.
@Anil_G whatever two values sum is 0 is consider as matched.
Like in 11.32 is repeated 22 times and one value is negative -11.32 so Status is updated for 2 value as matched rest are open item.
Similarly, 22.64 is repeated 208 times out of which 188 is -ve and 20 is positive , so status for match item is updated for 40 values remaining are open item.
if you are saying 1 is repeated 20 times and -1 is also repeated 20 times then matched item are 40.
or if you are saying 1 is repeated 1 time and -1 is repeated 20 times then only 2 values are consider as matched rest are open item.
Please try this
Dt.AsEnumerable.ToList.ForEach(sub(r) r("Status") = If(dt1.AsEnumerable.Where(function(x) cdbl(x(0).ToString) = 0-Cdbl(r(0).ToString) And Not String.IsNullOrEmpty(x(1).ToString)).Count>0,"Matched","Open item"))
cheers
In general we can do:
Group the amounts with the help of Math.Abs(…
Then split the the group members into the posivtive / negative value
Check how much positive values are reflected with the negative (e.g. with a stack approach)
Update the rows depending on the evaluation from above
@Anil_G Still not getting the correct output, it is still showing 11.32 values as matched.
You can also check the same at your end.
Find some starter Help:
Variables:
Flow:
Add Status Column (feel free to handle it conditional)
Group Data:
AmountGroups =
(From d In dtData.AsEnumerable
Group d By k=Math.Abs(CDbl(d("Amount").toString.Trim)) Into grp = Group
Select g = grp.ToList).toList
outerList: Groups, innerList: GroupMembers
GroupProcessing:
We loop over the AmountGroups | TypeArgument: List(Of DataRow)
posList = grp.Where(Function (x) CDbl( x("Amount").toString().Trim()) > 0).toList
negList = grp.Where(Function (x) CDbl( x("Amount").toString().Trim()) < 0).toList
MatchCount = new Int32(){posList.Count, negList.Count}.Min()
MatchList = posList.Take(MatchCount).concat(negList.Take(MatchCount)).toList
NonMatchList = posList.Skip(MatchCount).concat(negList.Skip(MatchCount)).toList
Then we update the Status by looping over the MatchList / NonMatchList
IPO Sample:
input:
processing: as described above
output:
100 has only 1 -100, so second 100 is unmatched
0 are undefined, so excluded it from the status flagging (you can handle as you want)
show us what you have modeled or share your xaml.
sry main excel file i am not allowed to share .
but sample excel file i have already shared
Palaniyappan
mpraveen1902
Anil_G
vinitha_yachamaneni
Nguyen_Van_Luong1
nikhil.girish
Yoichi
Gokul001
Parvathy
E.T.S