topic Re: How do I eliminate data with a certain string of characters? in QlikView

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

Mon, 27 Apr 2015 16:22:59 GMT

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

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

Mon, 27 Apr 2015 16:27:46 GMT

Hi Andrea,

use a where condition

Where

Isnum(ORDERID)

this will only load numeric values of the ORDERID field

hope that helps

Joe

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

Mon, 27 Apr 2015 16:30:31 GMT

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

Where Index(ORDERID,'F')=0

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

sunny_talwar — Mon, 27 Apr 2015 16:31:08 GMT

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

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

Mon, 27 Apr 2015 16:31:24 GMT

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

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

buzzy996 — Mon, 27 Apr 2015 16:39:24 GMT

try this,

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

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

Mon, 27 Apr 2015 17:11:07 GMT

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

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

sunny_talwar — Mon, 27 Apr 2015 17:12:59 GMT

LOAD yourFields

From XYZ

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

LOAD yourFields

From XYZ

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

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

ramoncova06 — Mon, 27 Apr 2015 17:22:38 GMT

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'

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

maxgro — Mon, 27 Apr 2015 17:27:57 GMT

what do you get with a

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

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

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

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

Tue, 28 Apr 2015 20:17:36 GMT

I re-tried this after we fixed some seemingly unrelated issues and this did the trick, thank you!