How to filter state wise based on column value

[Raja.G (Mr.RPABot)]

Hi Team,

I have one datatable i want to do filter state wise based on one particular column value

“BBB” column if “xxx”, “yyy” value found to find in “DDD” column if found USA state should get the data and print to US sheet in excel and if CANADA state found should get and write to “CA” sheet.

If “xxx” ,“yyy” column value and “DDD” column if not found in US and CA state we should skip.

NOTE: sample purpose i put only three US and CA state code it will come all the state code

USA- TX,LA,FL
CANADA - AB,MB,PE

example AA, BB not in US and CA state

Please help me anyone for this.

I have attached sample excel.
US and CA state list.xlsx (10.8 KB)

Regards,
Raja G

[rashmisingh2695 (Rashmi)]

Hi @Raja.G

Can you please try with this workflow
Filter_DT.xaml (10.2 KB)

[Raja.G (Mr.RPABot)]

Hi @rashmisingh2695 ,

Working but linq there is no data in datatable throw exception “The source contain no DataRows”

How handle this, Please help me on this.

Regards,
Raja G

[rashmisingh2695 (Rashmi)]

Hi @Raja.G

Please update the linq like below to handle empty rows

if(dtInput.AsEnumerable.Where(Function (row) (row("BBB").ToString.Equals("xxx") or row("BBB").ToString.Equals("yyy")) and StateMapping("US").ToArray.Contains(row("DDD").ToString)).Count>0,dtInput.AsEnumerable.Where(Function (row) (row("BBB").ToString.Equals("xxx") or row("BBB").ToString.Equals("yyy")) and StateMapping("US").ToArray.Contains(row("DDD").ToString)).CopyToDataTable,dtInput.Clone)

if(dtInput.AsEnumerable.Where(Function (row) (row("BBB").ToString.Equals("xxx") or row("BBB").ToString.Equals("yyy")) and StateMapping("CA").ToArray.Contains(row("DDD").ToString)).Count>0,dtInput.AsEnumerable.Where(Function (row) (row("BBB").ToString.Equals("xxx") or row("BBB").ToString.Equals("yyy")) and StateMapping("CA").ToArray.Contains(row("DDD").ToString)).CopyToDataTable,dtInput.Clone)