need if logic to derive field

[stonecold111]

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

[Claudiu_Anghelescu]

If(dirty1 = 'yes' or dirty2 = 'yes' or dirty3 = 'yes',
'dirty', 'clean') as Requiredfield

[stonecold111]

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)

[stonecold111]

is there anyone that can solve this????

[Lisa_P · Accepted solution]

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: