Create a pivot table without using excel scope activities

[Anish_Kumar2 (Anish Kumar)]

Hi team.

I have a data table with three columns: sf_name, new_ofbreach, and caseid.
BreachReport.xlsx (2.7 MB)

In pivot, I want sf_name as a row, new_ofbreach as a column, and caseid as a count of caseids.

Could someone please assist me with this? I’ve also attached an excel spreadsheet for that.
in that sheet sheet2 is the data and i want result as a sheet 1

[Anil_G (Anil Gorthi)]

@Anish_Kumar2

As you do not want to use pivot…try these steps

1. Read the input data into dt
2. Generate datatable activity and give string as "sf_Name" + Environment.NewLine + String.Join(Environment.NewLine,dt.AsEnumerable.Select(function(x) x("new_ofbreach").ToString).Distinct) and select csv parsing …and save as newdt
3. Now use assign activity with newdt = (From d In dt.AsEnumerable Group d By k=d("sf_Name").toString.Trim Into grp = Group Let ra = New Object(){grp.First()("sf_Name").ToString}.Concat(dt.AsEnumerable.Select(function(x) x("new_ofbreach").ToString).Distinct.Select(function(x) grp.Where(function(y) y("new_ofbreach").ToString.Equals(x)).Count)) Select r = DtOutput.Rows.Add(ra)).CopyToDataTable

Cheers

[Anish_Kumar2 (Anish Kumar)]

Can anyone idea about LINQ how we add Column (Nobreach), column (Breach) and save in data will Grand Total column.