I need colunt of month and unit combination?

Help Studio
, ,
1

i need colunt of month and unit combination
The image shows a table with the columns "Unit," "ItemCode," and "Month," listing various units alongside item codes and corresponding months. (Captioned by AI)

its for our output table how work this seniro kindly …

its our orginal datatable…

The table displays units grouped by their monthly activity from May to August, with some units having numerical values ranging from 1 to 3 in specific months. (Captioned by AI)

2

@domsmgtmeet22,

Follow this approach.

  1. Read input file into Datatable using Read Range
  2. Use this LINQ in assign activity
dtOutput = dtInput.AsEnumerable().GroupBy(Function(row) New With { Key .Unit = row.Field(Of String)("Unit"), Key .Month = row.Field(Of String)("Month") }).Select(Function(g) dtInput.Clone.LoadDataRow({g.Key.Unit, g.Key.Month, g.Count()}, False)).CopyToDataTable()

image

Now iterate through your template and check if the Init is available in the dtOutput or not. If available, take the month and it’s count and write it to your template.

Thanks,
Ashok :slight_smile:

3

i need to write template

4

@domsmgtmeet22 ,

Share the template

5

The table lists units with corresponding numerical entries for May, June, July, and August, indicating values for select months while others remain blank. (Captioned by AI)

6

’ Initialize your original DataTable (originalDT) and template DataTable (templateDT)
’ Assuming these DataTables are already populated in your workflow

’ Loop through each row in the template DataTable
For Each templateRow In templateDT.Rows

' Get the unit from the current template row
Dim unit As String = templateRow("Units").ToString()

' Count the occurrences for each month in the original DataTable based on the unit
Dim mayCount As Integer = originalDT.AsEnumerable().
                          Count(Function(r) r.Field(Of String)("Unit").ToString() = unit And r.Field(Of String)("Month").ToString().Contains("May"))

Dim juneCount As Integer = originalDT.AsEnumerable().
                           Count(Function(r) r.Field(Of String)("Unit").ToString() = unit And r.Field(Of String)("Month").ToString().Contains("June"))

Dim julyCount As Integer = originalDT.AsEnumerable().
                           Count(Function(r) r.Field(Of String)("Unit").ToString() = unit And r.Field(Of String)("Month").ToString().Contains("July"))

' Add more months as needed

' Assign the counts to the corresponding columns in the template DataTable
templateRow("May") = mayCount
templateRow("June") = juneCount
templateRow("July") = julyCount
' Continue for other months

' Calculate the Grand Total for all months
templateRow("Grand Total") = mayCount + juneCount + julyCount ' + other months as needed

Next

’ After populating the template DataTable, write it back to an Excel file
’ Use the Write Range activity to write the updated templateDT to Excel
’ Example:
’ Write Range Activity:
’ - Input: templateDT
’ - SheetName: “YourSheetName”
’ - StartingCell: “A1”
this code is show error