Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Creating Flag in script to achieve desired outcome with multiple conditions

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

    Or

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

Testing.PNG

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

1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

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

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

View solution in original post

2 Replies
Highlighted
Creator III
Creator III

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

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

View solution in original post

Highlighted
Creator III
Creator III

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

Dear Jaume,

Thank you for your correction. It works now!

Best Regards

QianNing