topic Re: Exclude certain data by row in QlikView

Exclude certain data by row

jenmclean — Wed, 04 Mar 2015 20:51:13 GMT

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!

Re: Exclude certain data by row

Wed, 04 Mar 2015 21:05:49 GMT

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

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

Re: Exclude certain data by row

saurabh5 — Wed, 04 Mar 2015 21:05:59 GMT

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

Anonymous — Wed, 04 Mar 2015 21:06:20 GMT

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

Re: Exclude certain data by row

jenmclean — Wed, 04 Mar 2015 21:24:54 GMT

Testing, thanks for you quick replies

Re: Exclude certain data by row

jenmclean — Wed, 04 Mar 2015 22:02:16 GMT

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

Anonymous — Wed, 04 Mar 2015 22:37:31 GMT

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.

Re: Exclude certain data by row

jenmclean — Wed, 04 Mar 2015 22:45:32 GMT

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

rwunderlich — Wed, 04 Mar 2015 23:43:46 GMT

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

Re: Exclude certain data by row

Thu, 05 Mar 2015 13:35:09 GMT

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

Re: Exclude certain data by row

jenmclean — Thu, 05 Mar 2015 15:39:35 GMT

Thanks everyone, I had to rethink the data but all your comments helped me so sort it out.