topic Re: Group by in Data Manager ?? in App Development

Group by in Data Manager ??

Manuel174102 — Mon, 25 Jul 2022 09:54:51 GMT

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

Re: Group by in Data Manager ??

Lisa_P — Mon, 25 Jul 2022 23:41:00 GMT

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;

Re: Group by in Data Manager ??

sidhiq91 — Tue, 26 Jul 2022 03:45:19 GMT

@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.

Re: Group by in Data Manager ??

Manuel174102 — Tue, 26 Jul 2022 11:18:51 GMT

thank u so much!! it worked!

Re: Group by in Data Manager ??

Manuel174102 — Tue, 26 Jul 2022 11:20:10 GMT

both ways worked, thanks!

Re: Group by in Data Manager ??

Manuel174102 — Tue, 26 Jul 2022 11:33:22 GMT

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

Re: Group by in Data Manager ??

Manuel174102 — Tue, 26 Jul 2022 11:33:41 GMT

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

Re: Group by in Data Manager ??

Lisa_P — Wed, 27 Jul 2022 00:52:33 GMT

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

Re: Group by in Data Manager ??

sidhiq91 — Wed, 27 Jul 2022 01:46:22 GMT

@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;