topic Using Set Analysis to count the number of rows with multiple conditions in QlikView

Using Set Analysis to count the number of rows with multiple conditions

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

Dear Qlikview Experts,

This is challenging for me and I have been stuck here for quite some time. I have tried but couldn't figure out the correct expressions to achieve my desired outcome.

In the back end, I wanted to create 2 text objects.

1st Text Object:

When 'Type' is equal to 'O' and 'S',

Count the correct number of rows if fields(Step1, Step2, Step3 &Step4) all contained null values

anyone of the 4 fields(Step1, Step2, Step3 &Step4) contained only 52 but not other numbers.

(So in this case, I shall get 15 rows as correct output.)

My current expression for this text object is:

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

2nd Text Object:

IF the condition on TOP have been met, then sum up the other 4 fields(Time1, Time2, Time3 &Time4) for that particular rows.

My current expression for this text object is:

=sum({<Type={'O','S'}>}interval(Timestamp#(Time0,'mm:ss'))+interval(Timestamp#(Time1,'mm:ss'))+interval(Timestamp#(Time2,'mm:ss'))
+interval(Timestamp#(Time3,'mm:ss'))+interval(Timestamp#(Time4,'mm:ss')))

Appreciate your time and effort to improve my current expressions to help me in getting my desired outcome. I had also attached a sample of qvw and txt log file for your reference. Thanks a ton in advance!

Best Regards

QianNing

Re: Using Set Analysis to count the number of rows with multiple conditions

sunny_talwar — Fri, 04 May 2018 02:23:21 GMT

Try using this script to create a flag in the script

t1:

LOAD *,

If((Len(Trim(Step1)) = 0 and Len(Trim(Step2)) = 0 and Len(Trim(Step3)) = 0 and Len(Trim(Step4)) = 0) or

((Step1 = 52 and RangeSum(Step2,Step3,Step4) = 0) or (Step2 = 52 and RangeSum(Step1,Step2,Step3) = 0) or (Step3 = 52 and RangeSum(Step1,Step2,Step4) = 0) or (Step4 = 52 and RangeSum(Step1,Step2,Step3) = 0)), 1, 0) as Flag;

LOAD @1 as Type,

@4 as Step1,

@5 as Step2,

@6 as Step3,

@7 as Step4,

@8 as Time0,

@9 as Time1,

@10 as Time2,

@11 as Time3,

@12 as Time4

FROM [Testing.txt]

(txt, codepage is 1252, no labels, delimiter is ';', msq);

And then this expression

=Count({<Type = {'O','S'}, Flag = {1}>} Type)

Re: Using Set Analysis to count the number of rows with multiple conditions

s10157754 — Fri, 04 May 2018 04:36:15 GMT

Dear Sunny,

Thank you so much for your solution! It works like a charm! Appreciated a lot!

Have a nice day!

Best Regards

QianNing