DataTable help - Getting the highest sum of results in a column

Hi

I want to get the value “Stationery” as string by adding up the results column and determining the “Order Topic” because the highest results are Stationery - 4, Food - 1 and Shape -1.

Whats the best way to do this?

@Steven_McKeering Do you want to get the Order Topic which has the Highest Value after Summing it’s Result Column Values ?

1 Like

Hey

Yes I do.

I want to get the ‘Order Topic’ with the highest value (after summing up the values from ‘Results’ column).

Sorry for the confusion.

@Steven_McKeering
Create a new Datatable with build Datatable and configure 2 columns, Topic, Count. - dtResult
Or use add data column in a new Datatable

Use an assign Activity as Second step
Left Side: dtResult
Right Side

(From d in yourorigindatatablevar.asenumerable
Group d by k=d(1).toString.trim into grp=Group
Let ra= {d(1), grp.sum(function (x) Cint(d(2).toString))}
Select dtResult.rows.add(ra)).CopyToDataTable

You will receive a statistic table with entries e.g. stationery, 4

This result Datatable can be later ordered e.g with Orderby Method

3 Likes

Thank you for the reply but this is a bit confusing for me and need some more help sorry :frowning_face:

I dont think I understamd the assign activity properly (screenshots below)

I have built a new datatable - dtResult.
my original datatable variable is called “dtDataRaw”

image

Assign activity:

Any further help would be appreciated…

@Steven_McKeering
Please incorporate the Line breaks as shown in my Post into your Statement
Line breaks after asenumerable and so on.

BTW. It IS better to explore new Things in a seperate xaml and get IT running. One this is done then this new Part can be integrated into the existing flow. This helps the isolated focussing on only one Task and removes the Risk of getting Blocked by the Others existing Code parts

Thank you for the advice. Agreed its better to prototype and then scale up.

I have made the line break changes but now getting a compiler error.

My variable is bolded:

dtDataRaw.asenumerable
Group d By k=d(1).toString.trim Into grp=Group
Let ra= {d(1), grp.sum(Function (x) CInt(d(2).toString))}
Select dtResult.rows.add(ra)).CopyToDataTable

Error message:
image

Variable screenshot:

What am I missing / need to change?

Looking into the Orderby Method.

I would then use the below expression in an ‘Assign’ activity:

NewStringVariable = (dtResults.OrderBy(Function(n) n).First).ToString

Any help is much appreciated :slight_smile:

@Steven_McKeering
Have again a Look to my Initial Post
IT will start with

(From d In dtDataRaw .asenumerable
Group d By k=d(1).toString…

For ordering the dtResult Datatable give a try on
dtResult.asenumerable.OrderBy(Function(x) CInt(x(1).toString)).Last() and Retriever the row with the highest count

Okay, so I have re-entered the “(From d In”

I am now getting this error message:

image

Thoughts?

Ensure following

Thank you

I have done that and now get this error message.

image

Do I just add ‘d’ as a variable?

@Steven_McKeering
Sorry some Typo and Copy issued happened by answering on Cellphone.

Replace within the Statement with following Line
Let ra= {grp.first()(1), grp.sum(Function (x) CInt(x(2).toString))}

Hey this worked! :clap:

For the ordering of dtResult datatable, do I just use an assign activity like this:
Left =
dtResult
Right =
dtResult.asenumerable.OrderBy(Function(x) CInt(x(1).toString)).Last()

"and Retriever the row with the highest count" (Is this how below?)

Then use another ‘Assign’ activity with a new string variable like this:
Left =
str_HighestTopic
Right =
dtResult.Compute(“Max(Result)”, “”).ToString()

What about the Idea to Sort the table on the Count assign IT on a new variable dtResultFiltered Just for prototyping?

Strange.

It worked and now its giving this error:
Assign: Cannot find column 2.

image

Assign activity:
image

@Steven_McKeering
can we do it by tomorrow?

Whenever it suits you @ppr

You are the one helping me :slight_smile:

perfect thanks. you can speed up the things by sharing a sample excel or a xaml filling up sampe date within a build data. I will pickup and work on this base

1 Like

Hello

I have attached my Exce reference list - Topic Reference List2.xlsx (18.1 KB)

Here is a sample of the Customer order PDF Customer example 1 (food).pdf (17.4 KB)

Here is my xaml - Forum - prototype 25 May 2020.xaml (15.7 KB)

Any help is really appreciated.