Linq query to filter data

Help Studio
, ,
1

Hi Team,

Could you please assist.

image

image

I am trying to achieve using below query

InputDT.AsEnumerable.GroupBy(Function(r) r(“Invoice”).ToString()).Where(Function(g) g.Any(Function(r)r(“Exception”).ToString=String.Empty) or (g.Any(Function(r) Not String.IsNullOrEmpty(r(“Remarks”).ToString)))).Select(Function(g) g.First()).CopyToDataTable

Hi @ppr
Could you please assist.

2

Hi @shaik_Azam ,

Could you let us know more about the Logic that needs to be applied for Vendor 2, Why was Successful chosen as the resultant, Is it because it is the last value of Vendor 2 or just because there is a Successful transaction for that particular Vendor ?

Also, the Vendor 4 Remarks column, Will the format be the same as provided in the Screenshot above ? So that we can use the Exception word as the Delimiter to get the details before it and create the Any (X or Y) statement from it ?

1 Like
3

Hi @supermanPunch,

Yes, for vendor 2 trying get only successful transaction row and Vendor 4 scenario - if both have exception , firstly attempting to achieve Y Exception 4. if not any of X exception or Y exception.

exception is not a delimiter , added to show format InputDT and outputDt.

TIA

4

Currently we dont have a sample which is showing:

  • Sucess/fail can occur in one group
    *Fail can also happen without Exception detail

So in an optimistic we can do:

(From d in InputDT.AsEnumerable
Group d By d("Invoice").ToString().Trim() into grp=Group
Let go = grp.OrderBy(Function (x) Convert.ToInt32(String.IsNullOrEmpty(x("Remarks").ToString.Trim)))
Select r = go.First()).CopyToDataTable
5

Hi @shaik_Azam ,

Check this below workflow attached, the query and input file used,
Uipath_Linq_FilterData.xaml (6.8 KB)

query:-
(From x In dt_Input.AsEnumerable
Group x By k=x(“Vendor”).ToString.Trim Into grp=Group
Let successRmk = grp.Any(Function(x) x(“Remarks”).ToString.Trim.ToLower.Equals(“successful”))
Let failedCnt = grp.Where(Function(x) x(“Remarks”).ToString.Trim.ToLower.Equals(“failed”)).ToArray.Count>1
Let removedEx = String.Join(" Or “,grp.Select(Function(x) If(System.Text.RegularExpressions.Regex.IsMatch(x(“Exception”).tostring.trim,“Exception\s+\d+”),System.Text.RegularExpressions.Regex.Replace(x(“Exception”).tostring.trim,“Exception\s+\d+”,”“).Trim,String.Empty)).ToArray())
Let ra = New Object(){
grp(0)(“Vendor”).ToString,
grp(0)(“Invoice”).ToString,
If(successRmk,“Successful”,“Failed”),
If(successRmk,”",If(failedCnt,"Any ( “+removedEx+” ) "+System.Text.RegularExpressions.Regex.Match(grp(0)(“Exception”).tostring,“Exception\s+\d+”).Value,System.Text.RegularExpressions.Regex.Match(grp(0)(“Exception”).tostring,“Exception\s+\d+”).Value))
}
Select dt_Output.Rows.Add(ra)).CopyToDataTable()

input file used:-
sample_input.xlsx (9.9 KB)

Hope this might help you :slight_smile:

3 Likes
6

Thanks everyone. This worked for me.

7

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