Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
stonecold111
Contributor 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
Employee
Employee

Re: need if logic to derive field

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

4 Replies
Claudiu_Anghelescu
Contributor III

Re: need if logic to derive field

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.
stonecold111
Contributor III

Re: need if logic to derive field

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
Contributor III

Re: need if logic to derive field

is there anyone that can solve this????

Employee
Employee

Re: need if logic to derive field

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