Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load WHERE, multiple criteria

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?

1 Solution

Accepted Solutions
marcarreras
Specialist
Specialist

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.

View solution in original post

9 Replies
AbhijitBansode
Specialist
Specialist

you can use same syntax as above.

Not applicable
Author

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

marcarreras
Specialist
Specialist

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.

View solution in original post

PrashantSangle

Hi,

Post your script.

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Not applicable
Author

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
Specialist
Specialist

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.

Not applicable
Author

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.

marcarreras
Specialist
Specialist

Joe , you are right. Thank you.

Not applicable
Author

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