How to create pivot table without using excel

[suraj_gaikwad]

Hi Everyone,

I have to create pivot table without using excel so basically i have to get count of total same po number and new sub lobs get count of duplicate sub lobs/total po number *100

Blow steps of how I’m using pivot table m

1. input Dt

![IMG20241104223721|666x500](upload://rMqozUVtLC2KxKL21Ippu2VK1l4.jpeg

2. Copy ‘New sub lob’ column and pasting in new sheet

3. insert pivot table

4. after insert pivot table its look like this
   

   

5. Copying created pivot table and as paste123 then
   Using formula count of new sub lob
   Firstcolumn/GrandTotal*100 then apply same formula for all colums final grand total should come 100

6. final table looks like this

Then i have to get one by one ‘Row lable’ value and count of sub lob and this value write on the url

Thanks

[Anil_G (Anil Gorthi)]

@suraj_gaikwad

Please try this in assign…first create a table with 3 columns first as string,next two as double

Newdt = dt.AsEnumerable.GroupBy(function(x) x(0).ToString).Select(function(x) newdt.LoadDataRow({x.Key,cdbl(x.Count),(x.Count/dt.RowCount)*100},False)).CopyToDataTable

Hope this helps

Cheers

[suraj_gaikwad]

@Anil_G

Giving error and which i used already in second code but I’m not able to extract row by row data

Thanks

[Anil_G (Anil Gorthi)]

@suraj_gaikwad

You just write select(function(x)) which does not even make sense…please use the proper code given above

Cheers

[suraj_gaikwad]

@Anil_G

Hi

What is the data type of newdt bcz it’s showing error

[Anil_G (Anil Gorthi)]

@suraj_gaikwad

it is datatable

@Anil_G

first create a table with 3 columns first as string,next two as double

cheers

[Kismet_Tosun (Kısmet Tosun)]

@suraj_gaikwad

Also you can check it

[suraj_gaikwad]

@Anil_G

Hi

Newdt = dt.AsEnumerable.GroupBy(function(x) x(0).ToString).Select(function(x)

This is not able to add in data table

[suraj_gaikwad]

We can do with activity it will take time. I have filter of every po number transaction then i have to create pivot table

@Kismet_Tosun

Thanks

[Anil_G (Anil Gorthi)]

@Anil_G

Newdt = dt.AsEnumerable.GroupBy(function(x) x(0).ToString).Select(function(x) newdt.LoadDataRow({x.Key,cdbl(x.Count),(x.Count/dt.RowCount)*100},False)).CopyToDataTable

Why are you giving only till function(x) …the above one is the full expression…please use the full expression

Cheers

[suraj_gaikwad]

Hi @Anil_G

I got the output but not as i looking

This output is coming

I’m looking output like

Second table

Then i have to get one value form pivot table

Thanks for the response

[Anil_G (Anil Gorthi)]

@suraj_gaikwad

Please share input and output in an exce

Cheers

[suraj_gaikwad]

Hi @Anil_G

I have attach file for reference

challenge (1).xlsx (16.6 KB)

So basically i have done only for single PO which i mentioned in sheet

Thanks

[suraj_gaikwad]

@Anil_G

Did you check the file

[suraj_gaikwad]

Hello @Anil_G @Kismet_Tosun

Any solution found.

Thanks

[Anil_G (Anil Gorthi)]

@suraj_gaikwad

Tried lookign at the sheet…not sure which is input which is ouput

can you please give only two sheet will try to figure from there and name them input and output

Or happy to connect you can send an invite

cheers

[suraj_gaikwad]

Hi @Anil_G

1. final result is a input file

2. sheet 1 is filter data only with Po number then i copy colum sub lob data and paste in sheet 1 tuen apply pivot table after its created sheet 3

3. Sheet 3 is final output

We will connect

Thanks

[Anil_G (Anil Gorthi)]

@suraj_gaikwad

looks liek you need this for each po

then you need to first loop based on po and then inside loop can get each pivot using above formula

1. for loop with Maindt.AsEnumerable.GroupBy(function(x) x("PO Column").ToString)
2. Inside loop currentitem.key will give the po column value and currentitem.GroupBy(function(x) x("Status Column").ToString).Select(function(x) newdt.LoadDataRow({x.Key,cdbl(x.Count),(x.Count/currentitem.Count)*100},False)).CopyToDataTable

type argument in for loop should be

[suraj_gaikwad]

Hi @Anil_G

Output is coming right but i have to process for single single po not all at a time

For example
PO first get from Excel1 then filter and pivot in Excel2 then process etc

Once we get final output then get the value of New sub lob and Percentage then write on url

Then go for next PO number in excel1 then filter and create pivot table in excel two

Like this

Thanks.

[suraj_gaikwad]

Hi @Anil_G

This is working but i need to do on single po number basis

I’m getting po number from different invoice on that i have to do pivot

Thanks