Select Action "Not Like"

[#1]

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!

[sunny_talwar]

May be like this

LOAD *

FROM ...

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

[#3]

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

[sunny_talwar]

May be like this:

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

[#5]

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.

[sunny_talwar]

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

[#7]

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

[sunny_talwar]

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, ' "|" ') & ' ") '

[#9]

(Image removed)

[sunny_talwar · Accepted solution]

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