topic Re: And condition in where clause not working ? confused , need help. in App Development

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

prahlad_infy — Wed, 25 May 2022 06:58:20 GMT

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

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

brunobertels — Wed, 25 May 2022 07:11:36 GMT

may be try

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

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

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

MarcoWedel — Wed, 25 May 2022 20:50:27 GMT

"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'

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

prahlad_infy — Fri, 27 May 2022 05:08:51 GMT

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 ?

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

marcus_sommer — Fri, 27 May 2022 06:47:12 GMT

You may try:

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

- Marcus

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

MarcoWedel — Fri, 27 May 2022 21:12:45 GMT

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'.

hope this helps

Marco