Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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_";
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.
Hey,
It is not working out. If I write that, it gives me the first table back
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;
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
;