Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
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

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#])

bill_markham

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');

bill_markham

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

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

Not applicable

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