Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jenmclean
Contributor III
Contributor III

Exclude certain data by row

This is a sample of a chart I am working on. In the Vendor Inv column as well as the Notes column, AP puts in the trigger CLEAR in either one or both of these fields. The end use of the report is to export into Excel with the final output being uploaded in the software that generates the actual printing of the checks.

I need to exclude any rows that have the trigger CLEAR in it.

How would I either script it or write an expression to handle this.

Thanks in advance!

Exclude CLEAR.png

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Match() is useful when you have multiple values to test against. It think it would be clearer to write using a simple <> operator.

Where TRA_VendInvoice <> 'CLEAR' AND TRA_VendPO <> 'CLEAR'


Your condition is an "AND" by the way.


-Rob

View solution in original post

10 Replies
Not applicable

You can exclude this rows in the load with a WHERE...

Example: Where not WildMatch(Notes,'*CLEAR*')

saurabh5
Creator II
Creator II

hi Jennie,

Can you provide the expression being used to create this straight table...the way to eliminate the 'clear' field value from the straight table is to  write a set analysis expression .....

something like below...

sum ({<[Vendor Inv]-={'CLEAR'}  and Notes-={'CLEAR'}>} [Check#])

Anonymous
Not applicable

I would use the WildMatch() function in the Load script to set a flag against the rows and then use Set Analysis to filter on that in your Chart expression.

You should be able to adapt this expression to your needs :

     =if ( WildMatch('vbn CLEAR mmm' , '*CLEAR*') , 'Discard' , 'Keep' )

jenmclean
Contributor III
Contributor III
Author

Testing, thanks for you quick replies

jenmclean
Contributor III
Contributor III
Author

OK, in the script, I do not need a wild match, need an exact match from both these fields. How would I write it, I have tried a couple of different ways...

Where not Match(TRA_VendInvoice,'CLEAR') or not Match(TRA_VendPO,'CLEAR');

Anonymous
Not applicable

If you mean that both must = CLEAR then try :

     Where not Match(TRA_VendInvoice,'CLEAR') and Match(TRA_VendPO,'CLEAR');

    

to exclude them from a Load.

jenmclean
Contributor III
Contributor III
Author

Needs to be an OR, one field may have CLEAR in it but not the other one and vice versa, data entry is inconsistent at times,

When I try the same code with OR instead of AND, does not seem to work, still show CLEAR in the notes field in the Chart

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Match() is useful when you have multiple values to test against. It think it would be clearer to write using a simple <> operator.

Where TRA_VendInvoice <> 'CLEAR' AND TRA_VendPO <> 'CLEAR'


Your condition is an "AND" by the way.


-Rob

Not applicable

It should work, check the QVW in the attachment...