Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Daniel1908
Creator
Creator

Count Empty Fields

Hello Together, 

I am using the below expression. 
Unfortunately the count for the empty fields (Address & Address_2) does not work.

[Address]-={'*'}
[Address_2]-={'*'}

Do you have any ideas? 

=Count(aggr(only(distinct{<[Product]={'1'},[Address]-={'*'},
[Product_Text]={"=WildMatch(Left(Product_Text,20),'*New*')"}>}Product_Number)
& only(distinct
{<[Product]={'1'},[Address_2]-={'*'},[Product_Text]={"=WildMatch(Left([Product_Text],20),'*New*')"}>}Product),Product,Options))

Kind Regards, 
Daniel

1 Solution

Accepted Solutions
marcus_sommer

I assume that the values aren't empty else NULL. NULL isn't be stored in any way could be therefore not selected and also not be excluded. AFAIK there are ways to calculate them with an indirect set analysis but it's not always trivial.

Easier is it in many cases to replace the NULL within the script with real values with any of the NULL variables or with measures like:

if(isnull(Field), 'NULL', Field) as Field

or 

if(len(trim(Field)), Field, 'NULL') as Field

which makes these values accessible. You may apply here any string you like maybe also ' ' and should such empty/default value be disturbing you may double the field and using one within the UI and the other only for the set analysis.

- Marcus

View solution in original post

4 Replies
MarcoWedel


{
  $<[Product]={'1'},[Product_Text]={"=WildMatch(Left([Product_Text],20),'*New*')"}>
  -
  $<[Address_2]={"*"}>
}

Daniel1908
Creator
Creator
Author

The expression shows ok but does not count the empty field for Adress and Adress_2.

Any further ideas?

Thanks for your help

Kind Regards, 
Daniel

marcus_sommer

I assume that the values aren't empty else NULL. NULL isn't be stored in any way could be therefore not selected and also not be excluded. AFAIK there are ways to calculate them with an indirect set analysis but it's not always trivial.

Easier is it in many cases to replace the NULL within the script with real values with any of the NULL variables or with measures like:

if(isnull(Field), 'NULL', Field) as Field

or 

if(len(trim(Field)), Field, 'NULL') as Field

which makes these values accessible. You may apply here any string you like maybe also ' ' and should such empty/default value be disturbing you may double the field and using one within the UI and the other only for the set analysis.

- Marcus

Daniel1908
Creator
Creator
Author

Thanks Marcus, 

This worked perfectly. 

Kind Regards, 
Daniel