How to creat a list from a DT and fill it and remove duplicates and sum the amounts

Help Studio
, ,
1

Hello everybody,

I’m working on a project where I need to filter a DT on 3 items and sum the amounts for each items ("Pôle).

I have this DataTable (out_dt_Strat), with 5 colunms, I need to filter for each “Pôle”, and sum all the amount (col Montant HT).

image
image919×140 4.35 KB

I’ve started to put the column “Pôle” into a List to have the list of each Pole (just once). Like this, if the client wants to add a new Pole, I can have it automatically.
For the moment, I can collect each “Pôle” in a list. see below

image
image871×543 16.6 KB

But then, I don’t know how to sum all the amount for each “Pôle”.

Coz at the end, I need to have the result for each “Pôle” in 3 variables
A = sum Montant HT of France, Belgium
B = sum Montant HT of America, Algeria, Austria
C = etc…

But I am stuck, I don’t know how to proceed. I’ve tryied with a formula dt.compute(Sum(colname) but it’s not working (or i’m using it in a wrong way)

Thank you in advance for your help

Again @ppr maybe you can help me out. :blush:

Kind regards

Najoua

2

Hi @najoua.abbaci

U can try this way

Before the for each loop u can intialise the dictionary

d= New Dictionary (Of String, Double)

Since u are looping through each element of list

Then inside the for each loop do the following:

  1. Filter the datatable out_dt_Strat with item
    using filter datatable activity and put the result of filter in dt3

  2. Then use the below assign query

sum= dt3.AsEnumerable().Sum(Function (row) CDbl(row(“Montant HT”)))

Where sum is double varible datatype.

Now to store the data u can dictionary concept here with key as item and value as sum

d(item)= sum

So in this way u can store the data

Hope it helps you

Mark it as solution if this resolves ur query

Regards

Nived N :robot:

Happy Automation :relaxed::relaxed:

2 Likes
3

Thank you @NIVED_NAMBIAR for your answer.
I tryied but it’s not working. I have a global sum (and i didn’t check if it is the sum of all the amount).
But I need a sum for each “Pôle”. But i’m trying to fix this. For the moment, not succeeded.

Kind regards
Najoua

4

Can u share the sample excel file ?

5

yes sure
here is a sample of the excel file
STRAT_Clean.xlsx (8.6 KB)

6

Hi @najoua.abbaci
Check this workflow
I had made the dictionary format that look finally like this

image

By use of the dictionary you can be able to retrieve the values very easily by using key

process1.zip (602.9 KB)

Mark it as solution if it resolves your query
Regards
Nived N
Happy Automation

7

Thank you very much @NIVED_NAMBIAR it seems to be working

I have one last (maybe simple) question, but how can i use B, F and L with their value, coz I need this values (B = 102075) to write it into the client software.

8

lets say u create an array of string

array_1={“B”,“F”,“L”}

Now use for each activity to loop through each element in array
(for each item in array_1)

then inside it use the following:
d1(item) to obtain the value and use some ui actions to write that one to a client software

Regards
Nived N

1 Like
9

ok thank you @NIVED_NAMBIAR

I have an error message, when I lauch with all my data, I have : “Assign: Conversion from string “” to type ‘Double’ is not valid.”
For the assign sum = dt1.AsEnumerable().Where(Function(row1) row1(“Pôle”).ToString.Equals(row(“Pôle”).ToString)).Sum(Function(row1) CDbl(row1(“Montant HT”)))

10

Can u show the excel file

I think there are some missing data in the Montant HT column

11

This is the excel fil with all the Montant HT
I just had to change the name of societies (for the client)STRAT_Clean.xlsx (10.4 KB)

12

I think it was because in the Montant HT their was an empty row. I removed it and it was working without this error message

13

Hi @najoua.abbaci
I think it is working fine for me
image
see the output i got

Regards
Nived N
Happy Automation

1 Like
14

Yes, for me too. Like I said I had an empty field but since i removed it, it is ok !
Thank you very much for your help ! @NIVED_NAMBIAR

2 Likes
15

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.