Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jenmclean
New Contributor II

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

Tags (2)
1 Solution

Accepted Solutions

Re: Exclude certain data by row

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

10 Replies
Not applicable

Re: Exclude certain data by row

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

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

saurabh5
Contributor II

Re: Exclude certain data by row

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

Re: Exclude certain data by row

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
New Contributor II

Re: Exclude certain data by row

Testing, thanks for you quick replies

jenmclean
New Contributor II

Re: Exclude certain data by row

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

Re: Exclude certain data by row

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
New Contributor II

Re: Exclude certain data by row

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

Re: Exclude certain data by row

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

Re: Exclude certain data by row

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

Community Browser