Excluding values on load from a field

[Seiya]

Have searched but the results I get do not work for me.

Current code from data load editor is as follows:

[bi_tools_bsj_data]:
LOAD 
	[serial_no],
	[cust_code],
....

I need to restrict exclude some two letter strings from cust_code as they are not required.

Code such as:

Where not Wildmatch(FieldName, '*ABC*');

Where  FieldName <>   'ABC' ;

from other solutions are not recognized in the load editor.

'Not' is not recognised and also since the solutions seem to be using semi-colons at the end not for use in load editor?

Is there anyway I can do this or change syntax somewhat to be applicable for my

[Arthur_Fong]

Try:

[bi_tools_bsj_data]:
LOAD 
	[serial_no],
	[cust_code]
from <Your Data SourcePath>
where FieldName <>  'ABC';

[Seiya]

Thanks that works.

What would the syntax be for multiple strings as unfortunately just adding , won't work.

[Arthur_Fong]

Try:

[bi_tools_bsj_data]:
LOAD 
	[serial_no],
	[cust_code]
from <Your Data SourcePath>
where not wildmatch(FieldName,  '*ABC*','*BCD*','*DEF*');

[Seiya]

error of wildmatch is not built in function name sorry.

tried all Caps, 'W' caps, and 'W' and 'M' caps but no dice.

[Arthur_Fong]

Try:

[data]:
LOAD 
	[serial_no],
	[cust_code]
from <Your Data SourcePath>;

noconcatenate
[bi_tools_bsj_data]:
LOAD 
	[serial_no],
	[cust_code]
resident data
where not wildmatch(FieldName,  '*ABC*','*BCD*','*DEF*');

Drop table data;

[Seiya]

Sorry it's starting to get a bit complicated. 😅

Let me just paste my full code below, noting that a "...." is just denoting fields I've hidden to save space.

[bi_tools_bsj_data]:
LOAD 
	[serial_no],
	[cust_code],
        ....
	[rece_ref],
	APPLYMAP( '__countryCodeIsoTwo2Polygon', UPPER([mfg_code]), '-') AS [bi_tools_bsj_data.mfg_code_GeoInfo];
SELECT "serial_no", 
	"cust_code", 
	....
	"rece_ref" 
FROM tcsHongKong.dbo."bi_tools_bsj_data"
where not wildmatch(cust_code,'*3U*','*BK*','*CA*','*CZ*','*HU*','*JD*','*MU*');

So for example in my script I already have the line "[bi_tools_bsj_data]" at the top and the "FROM" at the bottom of the

"SELECT" function.

If we were to switch things up exactly how it was in the example I'm sure things would break.

Again your help is much appreciated.

[Arthur_Fong · Accepted solution]

Try this:

Data:
LOAD 
	[serial_no],
	[cust_code],
        ....
	[rece_ref],
	APPLYMAP( '__countryCodeIsoTwo2Polygon', UPPER([mfg_code]), '-') AS [bi_tools_bsj_data.mfg_code_GeoInfo];
SELECT "serial_no", 
	"cust_code", 
	....
	"rece_ref" 
FROM tcsHongKong.dbo."bi_tools_bsj_data";

noconcatenate
[bi_tools_bsj_data]:
load * resident Data
where not wildmatch(cust_code,'*3U*','*BK*','*CA*','*CZ*','*HU*','*JD*','*MU*');
drop table Data;

[Seiya]

Thanks, much appreciated and it's all good now, though it does appear to load the data twice?

Guess we can't avoid that?

And for curiosities sake why does the original one liner not work and we have to enter an entire algorithm instead of just wildmatch xxx?

[Arthur_Fong]

Because you are connecting data via connectors.

SQL doesn't have wildmatch function(there might be but not using wildmatch as keyword) which returns invalid function.