Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
OR
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
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)
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)
Dear Sunny,
Thank you so much for your solution! It works like a charm! Appreciated a lot!
Have a nice day!
Best Regards
QianNing