LINQ query not working in assign activity

Help Studio
, , ,
1

Dim customerName As String = “Land of Toys Inc.” ’ Replace with the desired customer name

’ Filter the DataTable for the specified customer
Dim filteredData = sales_range.AsEnumerable().Where(Function(row) row.Field(Of String)(“CUSTOMERNAME”) = customerName)

’ Perform the LINQ query on the filtered data
Dim queryResult = (From row In filteredData
Group row By Key = New With {
.Customer = row.Field(Of String)(“CUSTOMERNAME”),
.Status = row.Field(Of String)(“STATUS”)
} Into Group
Select New With {
.Customer = Key.Customer,
.Status = Key.Status,
.TotalQuantity = Group.Sum(Function(r) r.Field(Of Integer)(“QUANTITYORDERED”))
}).ToList()
why is this not working ? is there any error , no matter what changes i do i get an error

2

sales_data_sample .xlsx (361.1 KB)
i use this data and what i want is i have customer column with around 10 different customers and i have a status column which has two values “shipped” and “cancelled” , there is also an quantity column where it specifies the amount of quantity shipped by the customer in each shipment.

4

@Harish_saravanan_R ,

2 questions…

What’s expected Output?
What’s there error?

Thanks,
Ashok :slight_smile:

5

Hi @Harish_saravanan_R

This is the VB code, use the code in Invoke Code activity not in Assign activity.

Hope you understand!!

6

@Harish_saravanan_R

try this

dt1 = dt.AsEnumerable().GroupBy(Function(row) New With {Key .Name = row.Field(Of String)("CUSTOMERNAME"), Key .Status = row.Field(Of String)("STATUS")}).Select(Function(group) dt1.Clone.LoadDataRow({group.Key.Name, group.Key.Status, group.Sum(Function(row) row.Field(Of Double)("QUANTITYORDERED")).ToString},False)).CopyToDataTable

here dt is your actual data and dt1 a datatble you need to build with 3 columns of string type usign build dtatable

image
image1363×493 28.5 KB

image
image372×522 10.6 KB

cheers

8

I get an error operator “=” is not defined for types datatable and datatable

9

@Harish_saravanan_R

dt1 should be on left of assign and dt expression on right of assign…please refer screenshot

cheers

10

@Harish_saravanan_R ,

Anil is mentioning to use like this.

image
image956×269 13.8 KB

Thanks,
Ashok :slight_smile:

11

Hi @Harish_saravanan_R

=> Build Data Table
image
Output-> resultDataTable

=> Read Range Workbook
Output-> dataTable

=> Use below syntax in Assign activity:

Save to -> resultDataTable

Value to Save ->  dataTable.AsEnumerable().Where(Function(row) row.Field(Of String)("STATUS") = "Shipped" Or row.Field(Of String)("STATUS") = "Cancelled").GroupBy(Function(row) New With {
        Key .Name = row.Field(Of String)("CUSTOMERNAME"),
        Key .Status = row.Field(Of String)("STATUS")
    }).Select(Function(Group) resultDataTable.LoadDataRow({ Group.Key.Name, Group.Key.Status,  CDbl(Group.Sum(Function(row) row.Field(Of Double)("QUANTITYORDERED")))}, False)).CopyToDataTable()

=> Write Range Workbook resultDataTable.

Output:
sales_data_sample .xlsx (364.8 KB)
sales_data_sample (1) is Input Sheet and Output is the Output Sheet

image
image806×615 33.5 KB

Sequence12.xaml (12.7 KB)

Regards

12

Thanks it is working and i got it.

1 Like
13

You’re welcome @Harish_saravanan_R

Happy Automation

1 Like
14

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