topic Re: Counting number of rows with multiple conditions in QlikView

Counting number of rows with multiple conditions

s10157754 — Wed, 25 Nov 2020 16:16:04 GMT

Dear Qlikview Experts,

My data model was pasted in below:

How can I count the correct number of rows if my 'Type' field is = DST or CST and my 'Step1 - Step4' fields doesn't contain any numbers?

So my desired outcome would be 3 rows?

Thank you for your time in advance!

Best Regards

Re: Counting number of rows with multiple conditions

tresB — Wed, 02 May 2018 05:30:39 GMT

At the back-end or UI?

Re: Counting number of rows with multiple conditions

s10157754 — Wed, 02 May 2018 06:06:36 GMT

Dear Tresesco B,

It was at UI.

My current expression looks like this (Where ProductNumber, Date & Time are my other fields):

count(aggr(if(Rangesum(if(Len(Step1)=0,1),if(Len(Step2)=0,1),if(Len(Step3)=0,1),if(Len(Step4)=0,1),if(Type='DST',1))=5,1),ProductNumber,Date,Time))

However, I was facing problems in adding 'or' into my 'Type' field. Is there any suggestions?

Best Regards

Re: Counting number of rows with multiple conditions

jonathandienst — Wed, 02 May 2018 06:06:43 GMT

In the back end

=Count({<Type = {'CST', 'DST'}>} If(Len(Step1) or Len(Step2) or Len(Step3) or Len(Step4), Null(), Type))

But a better approach is to create a flag field in the load script

LOAD...

If(Len(Step1) or Len(Step2) or Len(Step3) or Len(Step4), 0, 1) as FlagEmpty,

...

And then use expression

=Count({<Type = {'CST', 'DST'}, FlagEmpty = {0}>} Type)

Re: Counting number of rows with multiple conditions

jonathandienst — Wed, 02 May 2018 06:11:37 GMT

If ProductNumber is an identifying* field for the table, then a pure set analysis expression could be

=Count({<Type = {'CST', 'DST'}, ProductNumber = {"$(=(Len(Step1) or Len(Step2) or Len(Step3) or Len(Step4)) = 0)"} Step)

* a field whose values uniquely identify a row in the table, like a primary key or unique index in SQL.

Re: Counting number of rows with multiple conditions

jyothish8807 — Wed, 02 May 2018 06:12:49 GMT

You can create a straight chart at UI and set Type as Dimension and below as expression:

Or in a text box as you required,

sum(if( (Type ='DST' or Type=CST') and len(trim(Step4))=0 and len(trim(Step3))=0,1,0))

BR,

Re: Counting number of rows with multiple conditions

s10157754 — Wed, 02 May 2018 06:16:53 GMT

Dear Jonathan,

Thank you so much for the help! Your expression is better than my original one! Thank you!

Best Regards

QianNing

Re: Counting number of rows with multiple conditions

abhi1693r — Mon, 29 Jul 2019 19:40:30 GMT

I am trying to count with two conditions and getting results based only on the first condition.

=Count( DISTINCT {<A-={"DC"}>} {<B-={"KC"}>} A&'|'&B)

Can you help with this?

I want OR operator between the two conditions.