And condition in where clause not working ? confused , need help.

[prahlad_infy]

Hello Experts ,

I need to filter data based on two field values  (valuation1,amount1) .

if amount1 is blank along with valuation1 = '>20' then filter out those records . 

preferred_parterners1	subentity1	valuation1	amount1
conrad	CONRAD CORP A	>20
conrad	CONRAD CORP B	3m
conrad	CONRAD CORP C	4m
conrad	CONRAD CORP D	5-8m
conrad	CONRAD CORP E	1000m
minesoft corp	lily bay	>20
minesoft corp	lily bay	1m-2m
minesoft corp	lily bay	2-3b
minesoft corp	lo do vita	>20
minesoft corp	lo do vita	200
minesoft corp	pansy crest	>20
minesoft corp	primrose turf	>20
minesoft corp	primrose turf	2m-7m
minesoft corp	primrose turf	7500
minesoft corp	primrose turf	40500
minesoft corp	pansy crest	>20	10262.000000
Minsk	rock valley	>20
NA	luftansa	>20

But below code is not working 

op:
LOAD *
Where (len(amount)>0) and not Match(valuation,'>20') ;

LOAD * INLINE [

preferred_parterners, subentity, valuation, amount
conrad, CONRAD CORP A, >20,
conrad, CONRAD CORP B, 3m,
conrad, CONRAD CORP C, 4m,
conrad, CONRAD CORP D, 5-8m,
conrad, CONRAD CORP E, 1000m,
NA, luftansa, >20,
minesoft corp, primrose turf, 40500,
minesoft corp, primrose turf, 7500,
minesoft corp, primrose turf, >20,
minesoft corp, primrose turf, 2m-7m,
minesoft corp, lily bay, >20,
minesoft corp, lily bay, 1m-2m,
minesoft corp, lily bay, 2-3b,
minesoft corp, pansy crest, >20,
minesoft corp, pansy crest, >20, 10262.000000
minesoft corp, lo do vita, >20,
minesoft corp, lo do vita, 200,
Minsk, rock valley, >20,
];

Thank You 

[brunobertels]

hi 

may be try 

Where isnull amount and not Match(valuation,'>20')

or 

Where amount='' and not Match(valuation,'>20')

[MarcoWedel]

"if amount1 is blank along with valuation1 = '>20' then filter out those records ."

translates to

Where not (Len(amount)=0 and valuation='>20')

which is equivalent to 

Where Len(amount)>0 or valuation<>'>20'

[prahlad_infy]

Hi MarcoWedel ,

You quoted 

" 

which is equivalent to 

Where Len(amount)>0 or valuation<>'>20'

 "

Why cant it be  Where Len(amount)>0 and valuation<>'>20' . I am asking this because i wanted to filter records only  when both conditions are met .

But using   Len(amount)>0 or valuation<>'>20' , means if any of these two conditions are met then filter records .

Am i wrong in my understanding ? 

[marcus_sommer]

You may try:

Where if(len(trim(amount1)) = 0 and valuation1 = '>20', false(), true());

- Marcus

[MarcoWedel · Accepted solution]

Is this your expected result?:

using this script:

op:
LOAD *
Where Len(amount)>0 or valuation<>'>20';
LOAD * INLINE [
preferred_parterners, subentity, valuation, amount
conrad, CONRAD CORP A, >20,
conrad, CONRAD CORP B, 3m,
conrad, CONRAD CORP C, 4m,
conrad, CONRAD CORP D, 5-8m,
conrad, CONRAD CORP E, 1000m,
NA, luftansa, >20,
minesoft corp, primrose turf, 40500,
minesoft corp, primrose turf, 7500,
minesoft corp, primrose turf, >20,
minesoft corp, primrose turf, 2m-7m,
minesoft corp, lily bay, >20,
minesoft corp, lily bay, 1m-2m,
minesoft corp, lily bay, 2-3b,
minesoft corp, pansy crest, >20,
minesoft corp, pansy crest, >20, 10262.000000
minesoft corp, lo do vita, >20,
minesoft corp, lo do vita, 200,
Minsk, rock valley, >20,
];

If yes, then I understand you want to exclude any rows that have a blank amount AND a valuation of '>20'.
That's the same thing as saying you want to load any rows that either do have an amount (i.e. that are not null / have a length >0)  OR  a valuation other than '>20'.

see also:
https://en.wikipedia.org/wiki/De_Morgan%27s_laws

hope this helps

Marco