topic Re: Group by in data manager in App Development

Group by in data manager

Manuel174102 — Tue, 26 Jul 2022 11:36:43 GMT

Hello!!

I have the first table:

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

and I needed like this:

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

This is what I coded and its not working out

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

"Order Type Code"
Resident Table
Group By "Document No_";

Re: Group by in data manager

justISO — Tue, 26 Jul 2022 12:52:58 GMT

Hi, try this:

AmountCount: Load [Document No_], sum(Amount) as Amount, [Order Type Code] Resident Table Group By [Document No_], [Order Type Code];

If you are aggregating something (making measure), you need to group by all used dimensions.

Re: Group by in data manager

Manuel174102 — Tue, 26 Jul 2022 13:16:08 GMT

Hey,

It is not working out. If I write that, it gives me the first table back

Re: Group by in data manager

justISO — Tue, 26 Jul 2022 13:33:10 GMT

hmm, maybe no concatenation and distinct can change something:

NoConcatenate AmountCount: Load distinct [Document No_], sum(Amount) as Amount, [Order Type Code] Resident Table Group By [Document No_], [Order Type Code];

and don't forget to drop your resident Table if it is not needed anymore with script line

drop table Table;

Re: Group by in data manager

fallenangel6 — Wed, 27 Jul 2022 05:54:51 GMT

try this code. I'm getting the output.

LOAD
Nummer,
Sum(Amount) as totamt,
max("Order Type Code") as ordercode
FROM [lib://Downloads/Sample Data.xlsx]
(ooxml, embedded labels, table is Sheet1)
group by Nummer
;