Group two columns and get the number

[coder]

Hi,

How I can group the column of the contract and get the count of the Status?

for example:

> ContactNumber                       Status 
>       
>  Contract 1                          Submitted    
> 
>  Contract 2                         Not-Submitted
> 
> Contract  4                         Submitted
> 
> Contract 2                          Not-Submitted
> 
> Contract 1                          None

Output:

ContactNumber        Total      Submitted    Not-Submitted

Contract 1 

Contract 2 

Contract 3

Thank you

[Srini84 (Srinivas Kadamati)]

@coder

First use Build Datatable activity and declare your required Output Columns which are ContactNumber, Total, Submitted, Not-Submitted Let’s call this as dt_withoutData

Data as below

Then use your datatable with the results and let’s call this as dt_withData

Now use Assign activity dt_withoutData = and write below LinQ expression

Then your results in dt_withoutData is like below

Hope this may help you

Thanks,
Srini

[Yoichi]

Hi,

Can you try the following sample?

dtResult = dt.AsEnumerable.GroupBy(Function(r) r("ContractNumber").ToString).Select(Function(g) dtResult.LoadDataRow({g.Key,g.Count,g.Where(Function(r) r("Status").ToString="Submitted").Count,g.Where(Function(r) r("Status").ToString="Not-Submitted").Count},False)).CopyToDataTable

Sample20230616-6L.zip (9.0 KB)

Regards,

[coder]

thank you, can you please copy the code for assign, I get some errors.

[coder]

Thank you for your help.

but I want the total to be 1, not 2. like the total for values that are in this table only.

Thank you

[Srini84 (Srinivas Kadamati)]

@coder

Here is the code for your reference

Excel_Process.zip (8.7 KB)

Thanks,
Srini

[Yoichi]

Hi,

How about the following?

dt = dt.AsEnumerable.GroupBy(Function(r) r("ContractNumber").ToString).Select(Function(g) dtResult.LoadDataRow({g.Key,g.Where(Function(r) r("Status").ToString="Not-Submitted" OrElse r("Status").ToString="Submitted" ).Count,g.Where(Function(r) r("Status").ToString="Submitted").Count,g.Where(Function(r) r("Status").ToString="Not-Submitted").Count},False)).CopyToDataTable

Sample20230616-6Lv2.zip (9.1 KB)

Regards,

[coder]

Thank you, it’s working, can we also please add another column for percentages?

Number of Submitted/ total

Thank you

[Yoichi]

Hi,
Can you try the following sample？

Sample20230616-6Lv3.zip (9.1 KB)

Regards,

[coder]

Thank you for your help, sorry to distribute you. but I have one last question, can we also add the total at the end of each column with another row for percentages?

like this:

Status/ Total * 100

Thank you

[Yoichi]

Hope this helps you.

Sample20230616-6Lv4.zip (9.3 KB)

[coder]

Thank you so much,

its is possible to add the ContractNumber in the Template also like the Status?

so if the ContractNumber is not available in the column then we will write 0 or put the cell empty.

like this

Thank you

[Yoichi]

HI,

Can you check the following?

Sample20230616-6Lv5.zip (9.7 KB)

Regards,

[coder]

Thank you for your help.

but also I want to add the percentage here. its last thing

Thank you

[Yoichi]

Hi,

What value? Is it 100%? if so please use the following expression in AddDaaRow activity
If not, please modify the following last “100%” part into what you need.

{"%","",(100*dtResult.AsEnumerable.Select(Function(r) CInt(r(2))).Take(dtResult.Rows.Count-1).Sum/CInt(dtResult.Rows(dtResult.Rows.Count-1)(1))).ToString+"%", (100*dtResult.AsEnumerable.Select(Function(r) CInt(r(3))).Take(dtResult.Rows.Count-1).Sum/CInt(dtResult.Rows(dtResult.Rows.Count-1)(1))).ToString+"%","100%"}

[coder]

Thank you for your help. its working

[coder]

can you please explain to me these codes:

dtResult.AsEnumerable.Select(Function(r) r(0).ToString).ToArray()

And then why do you Clear Data Table?

arrContractNumber.Except(dtresult.AsEnumerable.Select(Function(r) r(0).ToString))

{“total”}.Concat(dtResult.Columns.Cast(Of DataColumn).Skip(1).Take(3).Select(Function(dc) dtResult.AsEnumerable.Sum(Function(r) CInt(r(dc))).toString)).ToArray()

{“%”,“”,(100dtResult.AsEnumerable.Select(Function(r) CInt(r(2))).Take(dtResult.Rows.Count-1).Sum/CInt(dtResult.Rows(dtResult.Rows.Count-1)(1))).ToString+“%”, (100dtResult.AsEnumerable.Select(Function(r) CInt(r(3))).Take(dtResult.Rows.Count-1).Sum/CInt(dtResult.Rows(dtResult.Rows.Count-1)(1))).ToString+“%”,“100%”}

Thank you

[Yoichi]

@coder

can you please explain to me these codes:

dtResult.AsEnumerable.Select(Function(r) r(0).ToString).ToArray()

And then why do you Clear Data Table?

The above expression returns data in column 0 as string array. It’s necessary to identify which contract number exists or not.

As the following logic appends grouping row, existing rows is unnecessary.

arrContractNumber.Except(dtresult.AsEnumerable.Select(Function(r) r(0).ToString))

This expression extract contractnumber which doesn’t exists in the datatable.

{“total”}.Concat(dtResult.Columns.Cast(Of DataColumn).Skip(1).Take(3).Select(Function(dc) dtResult.AsEnumerable.Sum(Function(r) CInt(r(dc))).toString)).ToArray()

This expression is additional row for total. The 1st item is “total” and calculate sum in each 2nd,3rd and 4th column.

{“%”,“”,(100dtResult.AsEnumerable.Select(Function(r) CInt(r(2))).Take(dtResult.Rows.Count-1).Sum/CInt(dtResult.Rows(dtResult.Rows.Count-1)(1))).ToString+“%”, (100dtResult.AsEnumerable.Select(Function(r) CInt(r(3))).Take(dtResult.Rows.Count-1).Sum/CInt(dtResult.Rows(dtResult.Rows.Count-1)(1))).ToString+“%”,“100%”}

This expression is additional row for percentage.The 1st item is “%”. The 2nd item is “”. 3rd and 4th
item is result of calculate percentage. 5th is “100%”

Regards,

[coder]

Thank you a lot for your help.