Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
You can exclude this rows in the load with a WHERE...
Example: Where not WildMatch(Notes,'*CLEAR*')
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#])
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' )
Testing, thanks for you quick replies
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');
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.
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
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
It should work, check the QVW in the attachment...