How do I eliminate data with a certain string of characters?

[#1]

Hi,

I have a certain amount of information associated to OrderID's - which are all numeric. However, we have test orders in the system (a lot of them) that all start with 'F'.

I want to try and eliminate these dummy orders and their associated information - but I'm not sure how to write that into the script. in SQL, I use the like function with a wildcard - but it looks like the wildmatch function in Qlikview doesn't duplicate this exactly.

Any suggestions?

Example:

DUMMY DATA        REAL ORDERID

     F653519                    1089136

[#2]

Hi Andrea,

use a where condition

Where

Isnum(ORDERID)

this will only load numeric values of the ORDERID field

hope that helps

Joe

[#3 · Accepted solution]

Or if you specifically need to check for the 'F', you can use index

Where Index(ORDERID,'F')=0

[sunny_talwar]

Are you trying to remove those rows of data where OrderID has 'F' in it?? If yes then may be use it like this

LOAD yourFields

From XYZ

Where not KeepChar(OrderID, 'F') = 'F'; (or Where not KeepChar(OrderID, 'F') <> 'F';)

HTH

Best,

Sunny

[#5]

That didn't work, none of the Test OrderID's were eliminated...

[buzzy996]

try this,

IF(wildmatch(DUMMYDATA,'F*'),NULL(),DUMMYDATA )AS NEWDUMMYDATA

[#7]

That is what I am trying to do, but neither of those worked...

[sunny_talwar]

LOAD yourFields

From XYZ

Where IsNull(KeepChar(OrderID, 'F');

or

LOAD yourFields

From XYZ

Where Len(Trim(KeepChar(OrderID, 'F')) = 0;

[ramoncova06]

do the only have and F associated with them ?

did the isnum(orderid), did not work ?  try with

where orderid * 1 >= 1, this should help identify the order that are numbers only

where left(UPPER(orderid),1) <> 'F' gets rid of everything that starts with a 'F'

[maxgro]

1)

what do you get with a

left(trim([DUMMY DATA],1))

?

some F? if yes use that as a filter in the where (QlikView)

2)

if your data come from a sql db you can still use sql function