Announcements
cancel
Showing results for
Did you mean:
Contributor

## Group by not giving correct result

Hi all,

I'm trying to do a group by on 2 fields (ReceptuurNr_2 and Receptuur_Grondstof.GRondstof) but i keep getting the wrong result in my table.

Table: Receptuur_Grondstof

I want a new table from a resident with for an example a row with the sum of the values in the Percentage column

TST01      TST02          TST03

27                351             9.80%

This is the code i used ...

TEST:

ReceptuurNr_2 as TST01,
Grondstof as TST02,
Num(Sum(Percentage), '#.##0,00%')  as TST03
Resident Receptuur_Grondstof group by ReceptuurNr_2, Grondstof;

This is the result:

I tried to use DISTINCT like this

Sum(DISTINCT Percentage) as TST03 with also a wrong result

Thanks for helping .

Labels (1)
• ### General Question

1 Solution

Accepted Solutions
MVP

Hi, this could be because there are many rows with the same values, you can check this adding a Count(Percentage) in the first table and see if it returns a number higher than 1.

In that case you can do a previous step to remove duplicate, load the table using a composite key and use exists() to avoid loading duplicates, something like:

``````LOAD receptuurNr_2 &'_'& Gronstof &'_'& ... OtherfieldstoCreateUinkeKey as KeyLoaded,
receptuurNr_2,
Gronstof,
...
resident ...
Where not Exists('KeyLoaded', receptuurNr_2 &'_'& Gronstof &'_'& ... OtherfieldstoCreateUinkeKey)``````
3 Replies
MVP

Hi, this could be because there are many rows with the same values, you can check this adding a Count(Percentage) in the first table and see if it returns a number higher than 1.

In that case you can do a previous step to remove duplicate, load the table using a composite key and use exists() to avoid loading duplicates, something like:

``````LOAD receptuurNr_2 &'_'& Gronstof &'_'& ... OtherfieldstoCreateUinkeKey as KeyLoaded,
receptuurNr_2,
Gronstof,
...
resident ...
Where not Exists('KeyLoaded', receptuurNr_2 &'_'& Gronstof &'_'& ... OtherfieldstoCreateUinkeKey)``````
Contributor
Author

Hi RubenMarin ... Thanks for the help ...

I think i don't completly understand what you mean ...

And I would like to have a new table with for each ReceptuurNr_2 the total Percentage of each Grondstof 5example Receptuur 27 / Grondstof 351 = 9,82%

I notice that there are a lot of lines in the table Receptuur_Grondstof  (496192)

When ik make a Worksheet like this and do an export to Excel i only have 6853 rows ???

I don't know what i'm doing wrong ...

Greetz,

Jochen

MVP

Hi, In a table it only shows different combinations of dimensions, so if a row it's duplicated x times you will only see it one time, following the code I posted befores and adapting it to fields you can crete a unique key to only load one row of ech combination, try to follow it and if you miss something ask about what part of the code you don't know how to adapt.

Regards.

Tags
Community Browser