Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I wonder if you could help as I’m poor at Qlik View expression writing. Below are 2 separate expressions to get the count of a column based on the occurrence of 2 other values:
I want to combine the 2, but don’t know how to use the ‘and’ function in Qlik View. I want to write 2 things..
Can anyone help? On a side point is there a dummies guide to the syntax for QV?
Many thanks
if(count({<DBDataCenter={'PA','PI'}>}logicaldb_rem_id)=0,'',count({<DBDataCenter={'PA','PI'}>}logicaldb_rem_id))
if(count({<State={'Migrated'}>}logicaldb_rem_id)=0,'',count({<State={'Migrated'}>}logicaldb_rem_id))
Maybe this:
count({<DBDataCenter={'PA','PI'},State={'Migrated'}>+<DBDataCenter={'PA','PI'},State-={'*'}>}logicaldb_rem_id)
Hi Gysbert,
This works a treat:
count({<DBDataCenter={'PA','PI'},State={'Migrated'}>}logicaldb_rem_id)
But when I try and turn it to blank values it doesn work. Am I missing something?
count({<DBDataCenter={'PA','PI'},State-={'*'}>}logicaldb_rem_id)
Examples above use set analysis, which can be rather difficult.
a more basic variant would be something like:
=count( if(DBDataCenter='PA' or DBDataCenter='PI' and State='Migrated', logicaldb_rem_id))
or with a distinct
=count(distinct if(DBDataCenter='PA' or DBDataCenter='PI' and State='Migrated', logicaldb_rem_id))
or else
=sum( if(DBDataCenter='PA' or DBDataCenter='PI' and State='Migrated', 1,0))
or if you want to avoid many OR statements, use
=count( if(mixmatch(DBDataCenter, 'PA','PI' >0, logicaldb_rem_id) )
a simple way to check if a field is blank, which also detects nulls is
len(State)> 1
or else a combination of isnull and = ''
Also have you looked at the qlikview reference manual , which comes with the installation package ?
If your blanks are not nulls but empty strings try State-={''}