Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Or if you specifically need to check for the 'F', you can use index
Where Index(ORDERID,'F')=0
Hi Andrea,
use a where condition
Where
Isnum(ORDERID)
this will only load numeric values of the ORDERID field
hope that helps
Joe
Or if you specifically need to check for the 'F', you can use index
Where Index(ORDERID,'F')=0
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
That didn't work, none of the Test OrderID's were eliminated...
try this,
IF(wildmatch(DUMMYDATA,'F*'),NULL(),DUMMYDATA )AS NEWDUMMYDATA
That is what I am trying to do, but neither of those worked...
LOAD yourFields
From XYZ
Where IsNull(KeepChar(OrderID, 'F');
or
LOAD yourFields
From XYZ
Where Len(Trim(KeepChar(OrderID, 'F')) = 0;
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'
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