topic Re: Select Action "Not Like" in QlikView

Select Action "Not Like"

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I want to create a select statement that excludes one data element. That data element has no numbers in it and I want to be able to select all records except for the one where ITMSNumber doesn't contain any numbers.

Can I use an expression like this in a select statement to achieve this end?

Thanks!

Re: Select Action "Not Like"

sunny_talwar — Fri, 27 Jan 2017 17:49:15 GMT

May be like this

LOAD *

FROM ...

Where Len(Trim(KeepChar(ITMSNumber, '0123456789'))) > 0;

Re: Select Action "Not Like"

Fri, 27 Jan 2017 17:51:20 GMT

Thanks Sunny. I want it to be an expression though, not part of the script.

Re: Select Action "Not Like"

sunny_talwar — Fri, 27 Jan 2017 17:53:20 GMT

May be like this:

=' (" ' & Concat(DISTINCT{<ITMSNumber = {"=Len(Trim(KeepChar(ITMSNumber, '0123456789'))) > 0"}>} ITMSNumber, ' "|" ') & ' ") '

Re: Select Action "Not Like"

Fri, 27 Jan 2017 17:57:01 GMT

Still didn't work. You gave me something like this a little while ago which worked and I still use but this one is not working in a "select in field" action.

Re: Select Action "Not Like"

sunny_talwar — Fri, 27 Jan 2017 18:05:47 GMT

Did you put these as double quotes or 2 single quotes?

=' (" ' & Concat(DISTINCT{<ITMSNumber = {"=Len(Trim(KeepChar(ITMSNumber, '0123456789'))) > 0"}>} ITMSNumber, ' "|" ') & ' ") '

They should be double quotes

Re: Select Action "Not Like"

Fri, 27 Jan 2017 18:14:12 GMT

I copy and pasted your line of code. It didn't work for me.

Re: Select Action "Not Like"

sunny_talwar — Fri, 27 Jan 2017 18:16:23 GMT

Would you be able to share a screenshot of the list box for ITMSNumber field? Also can you create a text box object with this as your expression:

=' (" ' & Concat(DISTINCT{<ITMSNumber = {"=Len(Trim(KeepChar(ITMSNumber, '0123456789'))) > 0"}>} ITMSNumber, ' "|" ') & ' ") '

Re: Select Action "Not Like"

Fri, 27 Jan 2017 18:20:20 GMT

(Image removed)

Re: Select Action "Not Like"

sunny_talwar — Fri, 27 Jan 2017 18:25:53 GMT

Can you try this:

='("' & Concat(DISTINCT{<ITMSNumber = {"=Len(Trim(KeepChar(ITMSNumber, '0123456789'))) > 0"}>} ITMSNumber, '"|"') & '")'

I removed the extra spaces between single quotes and double quotes

Re: Select Action "Not Like"

Fri, 27 Jan 2017 18:27:21 GMT

Obviously your expression should work. I'm a bit of a newb but it looks like it should work.

Re: Select Action "Not Like"

Fri, 27 Jan 2017 18:27:57 GMT

Worked!