topic Re: Exclude records with 'CLOSED' in text in QlikView

Exclude records with 'CLOSED' in text

Mon, 13 May 2013 13:46:40 GMT

I need to exclude all account names with 'CLOSED' typed into the account descripion. I cannot find the function to exclude e.g. NOT LIKE equivalent.

Will appreciate a tip or 2.

Regards

Re: Exclude records with 'CLOSED' in text

rwunderlich — Mon, 13 May 2013 13:56:04 GMT

Use either:

where not wildmatch(account, '*CLOSED*')

account NOT LIKE '*CLOSED*'

-ROb

Re: Exclude records with 'CLOSED' in text

Mon, 13 May 2013 13:59:25 GMT

load

[account names]

from tabletest

where not wildmatch([account names], '*CLOSED*')

Re: Exclude records with 'CLOSED' in text

Gysbert_Wassenaar — Mon, 13 May 2013 13:59:51 GMT

And if you need a case-sensitive match try where substringcount(account, 'CLOSED')=0

Re: Exclude records with 'CLOSED' in text

Mon, 13 May 2013 14:10:53 GMT

Thank you

Re: Exclude records with 'CLOSED' in text

Mon, 13 May 2013 14:17:06 GMT

Thank you

On Mon, May 13, 2013 at 3:00 PM, Gysbert Wassenaar <qcwebmaster@qlik.com

Re: Exclude records with 'CLOSED' in text

Tue, 14 May 2013 08:52:10 GMT

Hi Rob

I'm trying to select specific suppliers to list in pivot table.

Chart Properties >> Dimensions >> Used Dimensions = Supplier Name >> Enable Conditional Yes >>

Condition =

supplier_addr02 like '*NAMIBIA*'OR

supplier_addr02 like '*WINDHOEK*

Result - does not list any. Do I have to include the "display supplier name" somehow?

I'll appreciate assistance.

Re: Exclude records with 'CLOSED' in text

Gysbert_Wassenaar — Tue, 14 May 2013 09:00:28 GMT

The conditional display option for dimensions will show/hide the entire dimension. It does not filter dimension values. You can filter field values in a listbox by clicking on the magnifying glass icon and entering a search string. See the example below which filters Product where the name contains 'pp' or 'an'

For your supplier_addr02 field you can try (*NAMIBIA*|*WINDHOEK*)

Re: Exclude records with 'CLOSED' in text

Tue, 14 May 2013 10:30:11 GMT

Let me re-phrase my question.

If I want to sort supplier names based on a specific selection for a pivot table output ....

1. Can I somehow create the selection in the current sheet; or

2. Do I have to sort outside Qlikview and import to work from?

Re: Exclude records with 'CLOSED' in text

rwunderlich — Tue, 14 May 2013 13:48:09 GMT

You can create a calculated dimension that will filter values like:

if(wildmatch(supplier_addr02, '*NAMIBIA*', '*WINDHOEK*'), supplier_addr02)

-Rob