topic Re: Count empty rows using aggregation in QlikView

Count empty rows using aggregation

creepingdeath — Sat, 16 Nov 2024 17:38:08 GMT

I want to count rows where Technique_Detection is empty group by Numero_Sillon, Type_Horaire Technique_Detection

This was the expression I used :

count(Aggr(count( {<Technique_Detection={'GNSS','Localisateur','ObservationManuelle'}>} Technique_Detection),Date_Circulation,Numero_Sillon,CR_CI_CH,Type_Horaire))

and this is the unexpected result that I want to avoid.

Jalons Couverts : is the number of rows covered by "Technique_Detection" and should be 0 in this case

Total_Jalons : is the total calculated by this expression : count(TOTAL Aggr(count(Ordre),Date_Circulation,Numero_Sillon,CR_CI_CH,Type_Horaire))

Taux_de_couverture: is the coverage pourcentage wich should be 0%

Thanks in advance.

Re: Count empty rows using aggregation

creepingdeath — Sun, 10 Jan 2021 13:51:11 GMT

Any help ?

Re: Count empty rows using aggregation

edwin — Sun, 10 Jan 2021 14:39:39 GMT

a simple solution would be to add an EMPTY flag or count in your script:

load ...
if(isnull(Technique_Detection ),1) as BlankTech,
...

this way it will be a simple SUM(BlankTech) to get all blank values for that field instead of creating a complex expression

Re: Count empty rows using aggregation

edwin — Sun, 10 Jan 2021 14:53:19 GMT

if the field is blank and you count it - it will return 0, so in your statement

Jalons Couverts : is the number of rows covered by "Technique_Detection" and should be 0 in this case

there is something else going on, if you can show your expression for this, it would be helpful.

another way of counting nulls is this:

if you have Product and Type, and you want to count products with null TYpes:

count(Product) -all products
count(Type={"*"}Product) - all prpoducts with NON NULL types
count(Product) - count( Type={"*"}Product) - all products with NULL Types

Re: Count empty rows using aggregation

edwin — Sun, 10 Jan 2021 14:57:28 GMT

there will of course be a few other ways to do it and some may apply to your specific need based on your business rules and some may not.