Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

need if logic to derive field

Hi qlikss,

i  am attaching an excel file in i which need to derive a field from remaining field.

can someone write a logic for "required field"

 

if any one of the 3 dirty filed is yes then all orederno's should be dirty or else clean.

attached sample excel file

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Employee
Employee

Data:
LOAD orderno,
categeogy,
IF(dirty1='yes', 1,0) as d1,
IF(dirty2='yes', 1,0) as d2,
IF(dirty3='yes', 1,0) as d3,
dirty1,
dirty2,
dirty3,
amount

FROM
[C:\Users\axn\Downloads\dirty.xlsx]
(ooxml, embedded labels, table is Sheet1);

DC:
LOAD orderno,
IF((d1+d2+d3)>0, 1, 0) as DC
Resident Data;

Logic:
Load orderno, Sum(DC) as logic
Resident DC
group by orderno;

Left Join(Data)
Load orderno,
If(logic>0, 'dirty', 'clean') as Requiredfield
Resident Logic;

Drop tables DC, Logic;

Resulting table box:

dirty.PNG

View solution in original post

4 Replies
Highlighted
Specialist
Specialist

If(dirty1 = 'yes' or dirty2 = 'yes' or dirty3 = 'yes',
'dirty', 'clean') as Requiredfield
To help community find solutions, please don't forget to mark as correct.
Creator III
Creator III

but this is not what i want.  please see requiredfield again

if apply this logic i am goinge to get dirty clean dirty  clean clean

but i want dirty dirty dirty clean clean.

if anyone of the dirty is yes, then all order ids should be dirty. (i mean with same order id)

Highlighted
Creator III
Creator III

is there anyone that can solve this????

Highlighted
Employee
Employee

Data:
LOAD orderno,
categeogy,
IF(dirty1='yes', 1,0) as d1,
IF(dirty2='yes', 1,0) as d2,
IF(dirty3='yes', 1,0) as d3,
dirty1,
dirty2,
dirty3,
amount

FROM
[C:\Users\axn\Downloads\dirty.xlsx]
(ooxml, embedded labels, table is Sheet1);

DC:
LOAD orderno,
IF((d1+d2+d3)>0, 1, 0) as DC
Resident Data;

Logic:
Load orderno, Sum(DC) as logic
Resident DC
group by orderno;

Left Join(Data)
Load orderno,
If(logic>0, 'dirty', 'clean') as Requiredfield
Resident Logic;

Drop tables DC, Logic;

Resulting table box:

dirty.PNG

View solution in original post