Group by not giving correct result

[Jochen]

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:

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

[rubenmarin · Accepted solution]

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)

[Jochen]

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

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

This is the tabel i start from, i aded the KeyLoaded

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

[rubenmarin]

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.