Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Jochen
Contributor
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

Jochen_0-1633696733993.png

 

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:

Jochen_2-1633697359468.png

 

 

I tried to use DISTINCT like this 

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

Thanks for helping .

 

 

 

Labels (1)
1 Solution

Accepted Solutions
rubenmarin

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)

View solution in original post

3 Replies
rubenmarin

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
Contributor
Contributor
Author

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

Jochen_1-1633944786180.png

 

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 ???

Jochen_2-1633944969847.png

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.