topic Creating Flag in script to achieve desired outcome with multiple conditions in QlikView

Creating Flag in script to achieve desired outcome with multiple conditions

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

Dear Qlikview Experts,

I wanted to create a Flag in the script to get my desired output in the table. The condition for Flag to display as 1 would be:

1) If Step1 and Step2 and Step3 and Step4 all are blank cells

2) If anyone of the 4 fields (Step1 / Step2 / Step3 / Step4 ) Contains ONLY 52 but not other values.

The script that I am using currently is:

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 Step1,
@2 as Step2,
@3 as Step3,
@4 as Step4
FROM
(txt, codepage is 1252, no labels, delimiter is ';', msq);

I had tried to change my logic conditions in the script but still unable to get the correct output. I had attached a sample of qvw and txt log file to ease your reference. Appreciate your time to help me solve this problem! Thank you for your time in advance!

Best Regards

Re: Creating Flag in script to achieve desired outcome with multiple conditions

jaumecf23 — Tue, 08 May 2018 08:00:20 GMT

The problem that you use the function Rangesum with values that are not numbers and this is why is returned an incorrect value. Try the following code:

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 Len(Trim(Step2)) = 0 and Len(Trim(Step3)) = 0 and Len(Trim(Step4)) = 0) or

(Step2 = 52 and Len(Trim(Step1)) = 0 and Len(Trim(Step2)) = 0 and Len(Trim(Step3)) = 0) or

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

(Step4 = 52 and Len(Trim(Step1)) = 0 and Len(Trim(Step2)) = 0 and Len(Trim(Step3)) = 0), 1, 0) as Flag

;

LOAD

@1 as Step1,

@2 as Step2,

@3 as Step3,

@4 as Step4

FROM

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

Re: Creating Flag in script to achieve desired outcome with multiple conditions

s10157754 — Tue, 08 May 2018 08:18:04 GMT

Dear Jaume,

Thank you for your correction. It works now!

Best Regards

QianNing