topic Re: Excluding values on load from a field in QlikView

Excluding values on load from a field

Seiya — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Excluding values on load from a field

Arthur_Fong — Wed, 15 Jan 2020 05:14:53 GMT

Try:

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

Re: Excluding values on load from a field

Seiya — Wed, 15 Jan 2020 06:01:07 GMT

Thanks that works.

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

Re: Excluding values on load from a field

Arthur_Fong — Wed, 15 Jan 2020 06:02:48 GMT

Try:

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

Re: Excluding values on load from a field

Seiya — Wed, 15 Jan 2020 06:57:48 GMT

error of wildmatch is not built in function name sorry.

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

Re: Excluding values on load from a field

Arthur_Fong — Wed, 15 Jan 2020 07:02:59 GMT

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;

Re: Excluding values on load from a field

Seiya — Wed, 15 Jan 2020 07:29:22 GMT

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.

Re: Excluding values on load from a field

Arthur_Fong — Wed, 15 Jan 2020 07:32:15 GMT

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;

Re: Excluding values on load from a field

Seiya — Wed, 15 Jan 2020 07:46:18 GMT

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?

Re: Excluding values on load from a field

Arthur_Fong — Wed, 15 Jan 2020 07:56:35 GMT

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.