Age column in Data table

[Rahim_Bhatia]

I have an age column where the value is coming in years plus ‘yr’ included with it. For example 50 yr or 52 yr or 60 yr. I only need to extract people from that list that are between 50 and 52 years old. Can someone help me create a formula where it looks at only the numbers within the column and then compares if they are within the age of 50 and 52.

Thanks

[atomic]

Try this and see if it generates the expected result:
Use an assign activity, left side = datable variable named in this case “filteredData” and on the right side the rest of the Linq expression.

filteredData = dtAge.AsEnumerable().Where(Function(row) 
                   Int32.Parse(Regex.Match(row.Field(Of String)("Age"), "\d+").Value) >= 50 AndAlso
                   Int32.Parse(Regex.Match(row.Field(Of String)("Age"), "\d+").Value) <= 52).CopyToDataTable()

Replace “dtAge” with your datatable variable. “Age” is the name of the column I guess.
Also this would only work as long as the format is as described above.

[Yoichi]

Hi,

Considering there might be invalid data(including empty) and result has no rows, the following may be better.

 arrDr = dt.AsEnumerable.Where(Function(r) System.Text.RegularExpressions.Regex.IsMatch(r("age").ToString(),"\d+") AndAlso CInt(System.Text.RegularExpressions.Regex.Match(r("age").ToString(),"\d+").Value )>=50 AndAlso CInt(System.Text.RegularExpressions.Regex.Match(r("age").ToString(),"\d+").Value )<=52).ToArray()

Sample20230630-1L.zip (8.5 KB)

[Anil_G (Anil Gorthi)]

@Rahim_Bhatia

You can use the below in assign activity

requiredDT = dt.AsEnumerable.Where(function(x) Cint(x("Age").ToString>Replace("yr","").Trim)>50 AndAlso Cint(x("Age").ToString>Replace("yr","").Trim)<52).CopyToDataTable

cheers