Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
hi
may be try
Where isnull amount and not Match(valuation,'>20')
or
Where amount='' and not Match(valuation,'>20')
"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'
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 ?
You may try:
Where if(len(trim(amount1)) = 0 and valuation1 = '>20', false(), true());
- Marcus
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