Datatable Select fiter usage issue

[Latif]

Hi everyone,

Im trying to learn Select filter on datatable… due to lots of IF else statements I want to try Select filter…
I try to create something but look like im missing some thing…
Check my try to select filter …

Excel file:
ID
1
2
2
3
4
4

Sallery
2000
500
1000
4000
600
900

im trying to get all unique ID first… Assign act.
UniqueDT = dt1.DefaultView.ToTable(true,“ID”)

then i have created a for each loop on UniqueDT…
Urow = UniqueDT

within this for each loop i start with an Assign activity again… which can show me the unique id which has same value–

UniqueValue = dt1.Select(“[ID] = '”+row(“Column1”).ToString+“'”).CopyToDataTable

for each loop again on UniqueValue
Uvrow = UniqueValue

with this I create a message box with value
row(“Sallery”).ToString

And all this give me value one by one and its not that I want…

I want its show me ID 2 value same time means when it comes to ID 2 … it should show both 500 + 1000
and the same 2 row value on ID 4 = 700+900

can someone tell where/what I do wrong?

[Dave (David Wiebelhaus)]

I think you want to use a GROUP BY statement, rather than a select + filter statement. If I understand you correctly, your output should look like this, correct?

ID | Sallery
1  |  2000
2  |  1500
3  |  4000
4  |  1500

[Latif]

No Dave
My should look like this…
ID I Sallery
1 I 2000
2 I 500 +
1000
3 I 4000
4 I 600 +
900

I want that it give me ID 2 and 4 value at the same time … means it knew that ID 2 and 4 has 2 rows… so it should me both row value same time…

[Dave (David Wiebelhaus)]

that is a lot harder and at first glance i’m not sure how to do it, but I do for sure know that you won’t achieve the results using a simple select/filter statement. I’m thinking you need to convert the Sallery column to a string datatype if it isn’t already, then concatenate the values when grouping by ID (not sure if that’s possible? @ppr is a master with all things linq and will hopefully stop by to provide some input.

A less elegant way that would still work fine depending on the size of your dataset would be to create a second datatable consisting of only unique ID. It would then loop through each ID and have a nested For each row on the original datatable. It would check if ID from the first datatable matches, then put the salary in the new table and flip a flag so you know this row has an existing value. If it finds another ID in the first datatable then since the flag is flipped, it will append the string in dt2 (including the + sign) rather than overwriting the entire value. You could also have a ‘continue’ activity wrapped up in an if statement so once it gets past the matching ID it would continue onto the next ID in the loop. All of this would work reasonably fast if you made sure to sort the ID column by ascending before looping through the datatables.

Sorry for the long string of text. If it doesn’t make sense I can create a quick .xaml which will hopefully do a better job of what i am trying to say

[ppr (Peter Preuss)]

@Latif
input:

output:

find starter help here:
GroupBy_1Col_StringAggregation.xaml (8.8 KB)

[Latif]

Thanks but if you do not mind can explain this…

(From d In dtData.AsEnumerable
Group d By k=d(0).toString.Trim Into grp=Group
Let s = String.Join(“”,grp.Select(Function (g) g(1).toString.Trim).toArray)
Let ra = New Object(){grp.First()(0), s}
Select dtResult.Rows.Add(ra)).CopyToDataTable

I want to add more column to that table and read less column how can i do that…
and is there any option that Istead of 500 + 1000 I can do an Envoirement.newline?

[ppr (Peter Preuss)]

@Latif
please let us be clear on your scenario. Can you please share with us

input data: with variations
expected output result.

With this description we can help you directly and also faster.

Thanks

[Latif]

ok…
ID,Sallery, Desc
1 , 2000 , Sallery
2 , 500 , Bonus
2 , 1000 , Sallery
3 , 4000 , Sallery
4 , 600 , Bonus
4 , 900 , Sallery

In Output I want that when IT shows value of ID 2
Sallery = 500
Desc = Bonus
(Envoirement.Newline)
Sallery = 1000
Desc = Sallery
So that the user which see the record will understand that this person have 2 different transaction/rows in Excel.
And same for ID 4.

And if i have more column after Desc then I can easy extend my code…
Hope that you got what i means…

[ppr (Peter Preuss)]

if expected output is needed

• as text /string: share sample txt file with us
• table/excel: share sample excel in complete format (headers, all columns) with us

the clear requirement descriptions helps us to provide/suggest you the expected solution faster. Thanks

[Latif]

I read your comments on some other posts and I have noticed that you are not willing to help and try to put pressure on people to explain to you what they want even though they have already explained what they want.

It is OK if you do not want to help then do not comment - keep it simple.

The above description I did is what I want and Im 1000% sure you understod my need.
I can explain again…

I want when I do Output … in for each row. like
dtResult =
(From d In dtData.AsEnumerable
Group d By k=d(0).toString.Trim Into grp=Group
Let s = String.Join(“”,grp.Select(Function (g) g(1).toString.Trim).toArray)
Let c = String.Join(“”,grp.Select(Function (c) c(2).toString.Trim).toArray)
Let ra = New Object(){grp.First()(0), s, c}
Select dtResult.Rows.Add(ra)).CopyToDataTable

right now it shows me result like …
row.item(1)

I need this 500 in one line and 1000 in next line…
Plus I want to print the “Group c” value right after row.item(1), row.item(2)
like
1st row
Sallery = 500
Desc = Bonus
(Envoirement.Newline)
2nd row
Sallery = 1000
Desc = Sallery

[ppr (Peter Preuss)]

@Latif
For sure I / we will help you. But keep one thing in mind. With clear descriptions we can work faster and do bring better solution on the way. The time that we save in one thread we can use to answer / work on the next question. And thats All.

When I got you right the expected result was given by you as

@Latif

No Dave
My should look like this…
ID I Sallery
1 I 2000
2 I 500 +
1000
3 I 4000
4 I 600 +
900

And

@Latif

1st row
Sallery = 500
Desc = Bonus
(Envoirement.Newline)
2nd row
Sallery = 1000
Desc = Sallery

As requested for confimation was about following:
first description would look like this:

As the second description could be understood as a textual result (not table format) line by line

And on this your help was asked.
Thanks

[Latif]

OK let me clear. this excel data I want to register as a ServiceNow incident
So a user have 2 excel row means 2 different type of data in excel as par my example – one is Sallery and 2nd is Bonus… then I want to register in ServiceNow as One incident and add both Rows data into this incident.
So like …
Id 2
Sallery = 500
Desc = Bonus
(Envoirement.Newline)
Sallery = 1000
Desc = Sallery

The Assign value (which you help me with is correct )…
(From d In dtData.AsEnumerable
Group d By k=d(0).toString.Trim Into grp=Group
Let s = String.Join(“”,grp.Select(Function (g) g(1).toString.Trim).toArray)
Let ra = New Object(){grp.First()(0), s}
Select dtResult.Rows.Add(ra)).CopyToDataTable
It shows data like this.

And now my question was how can i seperate 5001000 into 2 lines ?
Plus if I have more column in that Excel sheet like Desc ect. Then how can i get that out with this group function… ?

Then I tried with modification on that your group function…
(From d In dtData.AsEnumerable
Group d By k=d(0).toString.Trim Into grp=Group
Let s = String.Join(“”,grp.Select(Function (g) g(1).toString.Trim).toArray)
Let c = String.Join(“”,grp.Select(Function © c(2).toString.Trim).toArray)
Let ra = New Object(){grp.First()(0), s, c}
Select dtResult.Rows.Add(ra)).CopyToDataTable

The bold lines I added… It gives med description out… Perfect… It shows what I want
Then question was…
How can I take out both value out in one type into or message box field. so that I present both row.item(1)(2) ect.

So that it look like this…

let see if i made good description

[ppr (Peter Preuss)]

@Latif
find attached an adopted implementation that is

• decomposing the LINQ and focusing on the data entry
• it allows you to get control and to adopt as by your needs

Find XAMl here:
Latif_GroupColConcatFlow.xaml (16.4 KB)

Also feel free to do some experiments with following:
(From d In dtData.AsEnumerable
Group d By k=d(0).toString.Trim Into grp=Group
Let s = String.Join(Environment.NewLine,grp.Select(Function (g) g(1).toString.Trim).toArray)
Let c = String.Join(Environment.NewLine,grp.Select(Function ( c ) c(2).toString.Trim).toArray)
Let ra = New Object(){grp.First()(0), s, c }
Select dtResult.Rows.Add(ra)).CopyToDataTable

[Latif]

I tried with this Envoirement.NewLine …
(From d In dtData.AsEnumerable
Group d By k=d(0).toString.Trim Into grp=Group
Let s = String.Join( Environment.NewLine ,grp.Select(Function (g) g(1).toString.Trim).toArray)
Let c = String.Join( Environment.NewLine ,grp.Select(Function ( c ) c(2).toString.Trim).toArray)
Let ra = New Object(){grp.First()(0), s, c }
Select dtResult.Rows.Add(ra)).CopyToDataTable
and it gives the value on next line. which is good
But in my for each loop when i want to print two index value on same box it show me wrong answer…

Answer look like this…

I want it be
Bonus = 500
Sallery = 1000

hope you can see the error.

[ppr (Peter Preuss)]

@Latif
May I ask you on your feedback related to the last provided XAML. Thanks

[Latif]

Hi robot master.
It good that it show the value… but the logic it totally changed now…

I want to go through whole Excel and create SN case on each row and where first and 2nd row ID is same it should do collection of data… and then go with next row… so i have to register data row by row in serviceNow…
I do not know if im confused with your help as we was doing right but then we changed the methord to for each instead of for each row and then its not right i feel.

[ppr (Peter Preuss)]

@Latif
Groups is a list(Of List(of DataRows) and is reflecting

each Group Member Rows - inner List
each Group - outerlist

• as we iterate over the Groups we do use the for each
• inside we thee loop we can
  • doing the entire group processing directly OR
  • if needed doing in an additional nested for each / for each row ( was also mentioned in one of the comments)

it is up to you to create the excel or to implement the ServiceNow entering within the blocks

the last XAML was producing following EXCEL

DataTable producing above result from screenshot can be done also with following statement:

using this variable:

Can you give a feedback on how close the result is to the expected output / processing result?

Thanks

BTW: My name is Peter or you can reference me with ppr. As you have seen by many others members as well the line Robot Master comes from a group membership

[Latif]

HI Peter,
I used your this solution

to work on and I did modification related to my project…
I have attached my workflow… where im not using build datatable.

1. Data comes from Excel file…
2. I count the column to find the last column which is empty and giving this a name (to add data when im done with project)
3. I have used your group function and for each loop… and doing my project…
4. In last I write that table into same Excel file but on another sheet because I want to add the SN number which im getting from Get Text.

But is it possible that I can only write SN into Sheet1 on each row and not whole table like it update the row value in of column SN.

Check my files now.

TableWithData.xlsx (9.9 KB) GroupBy_1Col_StringAggregation.xaml (47.1 KB)

[ppr (Peter Preuss)]

@Latif

But is it possible that I can only write SN into Sheet1 on each row and not whole table like it update the row value in of column SN.

technically should this be possible and multiple ways could be found. Find some starter help here, not implementing the detail integration code part but show casing you the main building blocks with a quick prototype:
Latif_UpdateSN.xaml (13.1 KB)

[Latif]

It’s to much complicated now and I did not understand how can i arrange acording to my that script