Filtration plus conditioning using linq for datatable

Hi!

I have a data table wherein,


In this, I have to collect row 1,Col K’s (Q), value in Row S (-0.25) and that has to meet a condition which is, Row S value should be >= -0.25.

AND

If there are Duplicate rows i.e(Q) all the Q rows values must be collected and stored in a variable.
Variable=-0.25,100…etc

This has to be performed for all Rows of Col K with Col S

P.S: Excel Activities are not to be used

Linq is preferred

Kindly, help me out as I am a novice in this.

Regard’s
Sudharsan S

@Sudharsan_S

Welcome to the community

Follow the steps

  1. Use build datatable activity and add two columns with string type and store in outdt
  2. Read the data from excel into datatable dt
  3. Use the following in assign
    outdt = (From d In Dt.AsEnumerable() Group d By k=d("columnKName").toString.Trim Into grp = Group Let coun = String.Join(",",grp.Where(function(x) cdbl(x("ColumnSName").ToString)>=-0.25).Select(function(x) x("ColumnSName").ToString)) Let ra = New Object(){k,coun} Select r = outDt.Rows.Add(ra)).CopyToDataTable()

Hope this helps

Cheers

any other option?

hi, it’s throwing an error that operator = is not defined for system.data.DataTable

Hi @Sudharsan_S ,

Could you let us know what is the End output that is Expected ?

@Sudharsan_S

Outdt should be on left side of the assign actovty…and remaining on the right side…

And I guess you wanted to use linq instead of loops and excel activities

Cheers

ok got, it
I got the result also

but negative values are not printing…and in those places it null

@Sudharsan_S

Are you sure…can you check the input if those are there and if you can share a file I can verify from my end…I did verify with sample data and I could see all

cheers

yes I am sure, sharing the file is an issue, but null values are printing instead of negative numbers.

if u have any updates on it, pls let me know

cheers,

@Sudharsan_S ,

Did you test the Expression with the Data that was mentioned or is it a Different data that you are using ?

Let us know if a Different data was used, what are the difference between the two.

@Sudharsan_S

Is it possible to share the data how it looks after reading into datatable…or can you create a dummy file and run on it and check…if there also it is failing then please attach the dummy file you have tested on…so that we can check from our end

cheers

the dummy excel
New Microsoft Office Excel Worksheet.xlsx (8.3 KB)
I did try the other method, still failed…
tried to change the data types as well and tried, still it did not work…

Hi,
I am using different data…
it’s not the above-mentioned data.
The issue that I am facing is when running, the values in S1,S2 are not being written,instead its a empty cell

@Sudharsan_S ,

Highlighting on the Question asked in the previous post, Could you also provide the Expected Output for the Input data you have provided ?

hey anil,
just hold off on this…
I think there might be a mistake on my end…
If it is, extremely sry for the inconvenience caused.

will let u know in a few mins…

Regards,

1 Like

@Sudharsan_S

Take your time…let us know the findings if its working or not…we cna help you accordingly

Cheers

Its working, its was mistake on my end.

Thank you so much :raised_hands:

Regards,

1 Like

@Sudharsan_S

Good to know

Happy Automation

Please mark the solution so that others can get help as well

Cheers

Will do.

Cheers,

1 Like