topic Re: Load WHERE, multiple criteria in QlikView

Load WHERE, multiple criteria

Wed, 18 Feb 2015 10:05:26 GMT

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?

Re: Load WHERE, multiple criteria

AbhijitBansode — Wed, 18 Feb 2015 10:08:06 GMT

you can use same syntax as above.

Re: Load WHERE, multiple criteria

Wed, 18 Feb 2015 10:14:52 GMT

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

Re: Load WHERE, multiple criteria

Anonymous — Wed, 18 Feb 2015 10:16:33 GMT

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.

Re: Load WHERE, multiple criteria

PrashantSangle — Wed, 18 Feb 2015 10:22:44 GMT

Hi,

Post your script.

Regards,

Re: Load WHERE, multiple criteria

Wed, 18 Feb 2015 10:30:15 GMT

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

Re: Load WHERE, multiple criteria

richard_chilvers — Wed, 18 Feb 2015 10:31:30 GMT

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.

Re: Load WHERE, multiple criteria

Wed, 18 Feb 2015 10:31:46 GMT

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.

Re: Load WHERE, multiple criteria

Anonymous — Wed, 18 Feb 2015 10:33:52 GMT

Joe , you are right. Thank you.

Re: Load WHERE, multiple criteria

Wed, 18 Feb 2015 10:44:12 GMT

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