Load WHERE, multiple criteria

[#1]

Hi,

How do I state a multiple criteria where clause when loading data?

I need to exclude specific transactions

WHERE Sender = XXXX and Amount <0 and Right(Amount,4)='00.00'

Any ideas?

[AbhijitBansode]

you can use same syntax as above.

[#3]

Hi Abhijit,

from my understanding that will exlude all of the different criterias separately. Meaning all transactions where sender = XXX, all negative amounts and so on.

I want to exclude ONLY the transactions where all of the three criterias are filfilled. Meaning negative amounts from sender XXX which ends with 00.00

//O

[Anonymous · Accepted solution]

Hi Olle,

Use Match function in the where statement to reach your requirement (right function does not exist in QV).

Just try this:

tabledata:

LOAD *, Sender, Amount

FROM...

WHERE not(Sender='XXX' and Amount<0 and right(Amount, 4)= '00.00');

You can also use wildmatch function:

...

WHERE not(Sender='XXX' and Amount<0 and wildmatch(Amount, '*00.00')=1);

You also can use Mid function:

...

WHERE not(Sender='XXX' and Amount<0 and mid(Amount, len(Amount)-4, 5)='00.00');

Marc.

[Edit] I added not() function in order to exclude the records that accomplish both 3 conditions.

[PrashantSangle]

Hi,

Post your script.

Regards,

[#6]

Hi Olle,

Can use the criteria you specified with a not statement.

WHERE NOT (Sender = XXXX and Amount <0 and Right(Amount,4)='00.00')

hope that helps

Joe

[richard_chilvers]

I think your syntax is correct. Because you use AND, all the criteria must match for a record to be selected.

If you were using OR between criteria, the results would be different. But, as marc says, you must use valid functions etc.

[#8]

Marc,

page 337 of QV manual, you'll find right is a valid function

right( s , n )

Substring of the string s. The result is a string consisting of the last n characters of s.

Example:

right('abcdef',3 ) returns 'def'.

right( Date,2 ) where Date = 1997-07-14 returns 14.

[Anonymous]

Joe , you are right. Thank you.

[#10]

ha touché

I should also say that "match(Amount, '00.00')=1" won't give you the desired result.

Match will look at the entire value rather than a section within it. You are meaning wildmatch instead I think.

wildmatch(Amount, '*00.00')=1

Joe