Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:
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)
is there anyone that can solve this????
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: