Group by in Data Manager ??

[Manuel174102]

Hello everyone, 

I have the following problem in the data manager

my table appears like this:

Nummer	Amount
VR1	3
VR1	4
VR2	6
VR2	3
VR2	3
VR3	6
VR3	6
VR3	5
VR3	3

and I need it like this: 

Nummer	Amount
VR1	7
VR2	12
VR3	20

I tried using a combination of Sum() and if() but it does not work out. it doesnt allow me to use the function sum() in the data manager. 

what could i do? 

greetings

[Lisa_P · Accepted solution]

One way to solve this is to use the Data Load Editor to create an aggregation table.

Create a new section in the script after the Auto-generated section and add this script:

Load Nummer,
           Sum(Amount) as NumAmount
Resident OriginalTable;

[sidhiq91 · Accepted solution]

@Manuel174102  Please use the Data load Editor and enter the script as shown below. It should give you the desired output:

NoConcatenate
Temp:
Load * Inline [
Nummer, Amount
VR1, 3
VR1, 4
VR2, 6
VR2, 3
VR2, 3
VR3, 6
VR3, 6
VR3, 5
VR3, 3
](delimiter is ',');

NoConcatenate
Temp1:
Load Nummer,
Sum(Amount) as Total_Amount
Resident Temp
Group by Nummer;

Drop table Temp;

Exit Script;

If this resolves your issue, please like and accept it as a solution.

[Manuel174102]

thank u so much!! it worked!

[Manuel174102]

both ways worked, thanks!

[Manuel174102]

Now I have another question, 

I have the first table, and I want to have it organised as the second one. It is a similar problem. 

This is that I coded and it is not working out :

AmountCount:
Load
      Sum(Amount),
      "Document No_"

       "Order Type Code"

Resident Original Table
Group By "Document No_";

Nummer	Amount	Order Type Code
VR1	3	4
VR1	4	4
VR2	6	1
VR2	3	1
VR2	3	1
VR3	6	2
VR3	6	2
VR3	5	2
VR3	3	2

Nummer	Amount	Order Type Code
VR1	7	4
VR2	12	1
VR3	20	2

Thanks in advance

[Manuel174102]

Now I have another question, 

I have the first table, and I want to have it organised as the second one. It is a similar problem. 

This is that I coded and it is not working out :

AmountCount:
Load
      Sum(Amount),
      "Document No_"

       "Order Type Code"

Resident Original Table
Group By "Document No_";

Nummer	Amount	Order Type Code
VR1	3	4
VR1	4	4
VR2	6	1
VR2	3	1
VR2	3	1
VR3	6	2
VR3	6	2
VR3	5	2
VR3	3	2

Nummer	Amount	Order Type Code
VR1	7	4
VR2	12	1
VR3	20	2

Thanks in advance

[Lisa_P]

I'm not sure why you would want to do this ?

[sidhiq91]

@Manuel174102  Code will remain the same, please see below. But sorting of the columns can be  done in straight table in the front end as per your wish. Let me know if you need any help on that.

NoConcatenate
Temp:
Load * Inline [
Nummer, Amount, Ordertypecode
VR1, 3,4
VR1, 4,4
VR2, 6,1
VR2, 3,1
VR2, 3,1
VR3, 6,2
VR3, 6,2
VR3, 5,2
VR3, 3,2
](delimiter is ',');

NoConcatenate
Temp1:
Load Nummer,
Sum(Amount) as Total_Amount,
Ordertypecode
Resident Temp
Group by Nummer,Ordertypecode;

Drop table Temp;

Exit Script;