topic Help with basic QlikView Expression in QlikView

Help with basic QlikView Expression

Thu, 15 Aug 2013 11:35:03 GMT

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:

DB Data Center
State

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

1. If DBDataCenter = PA or PI and State = Migrated.....then count logicaldb_rem_id etc.
2. If DBDataCenter = PA or PI and State = Is Null / Blank.....then count logicaldb_rem_id etc.

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

Re: Help with basic QlikView Expression

Gysbert_Wassenaar — Thu, 15 Aug 2013 11:52:32 GMT

Maybe this:

count({<DBDataCenter={'PA','PI'},State={'Migrated'}>+<DBDataCenter={'PA','PI'},State-={'*'}>}logicaldb_rem_id)

Re: Help with basic QlikView Expression

Thu, 15 Aug 2013 14:20:49 GMT

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)

Re: Help with basic QlikView Expression

Thu, 15 Aug 2013 15:19:23 GMT

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 ?

Re: Help with basic QlikView Expression

Gysbert_Wassenaar — Thu, 15 Aug 2013 15:27:18 GMT

If your blanks are not nulls but empty strings try State-={''}