topic Re: Load Script, using where not match in QlikView

Load Script, using where not match

ljackson — Mon, 11 Nov 2013 10:49:39 GMT

I have the following script, that works perfectly until I add in the 'where not match' string at the bottom.

All records have one of 4 variables of field "analysis-cat" - d, c, f or blank. I am only interested in the records where the "analysis-cat" is blank, so to speed up the load process I want it to ignore records with a d, c, or f entry. When I load I get a syntax error, for the life of me cannot work out what I am doing wrong!

Can anyone help? Many thanks.

LOAD

"cust-code" as CustCode,
"analysis-cat",
"doc-date" as Date,
rsv;

SQL

SELECT

"ac-chg",
"analysis-cat",
"comm-val",
"cust-code",
"d-invoice",
"doc-date",
"input-vat",
"output-vat",
"pl-val",
"print-ind",
rsv,
"rsv-inc",
"sl-val",
wgp

FROM PUB.invanal

where not(match("analysis-cat",'d','c','f'));

Re: Load Script, using where not match

kaushiknsolanki — Mon, 11 Nov 2013 10:52:30 GMT

Hi,

Try this.

LOAD

"cust-code" as CustCode,
"analysis-cat",
"doc-date" as Date,
rsv;

SQL

SELECT

"ac-chg",
"analysis-cat",
"comm-val",
"cust-code",
"d-invoice",
"doc-date",
"input-vat",
"output-vat",
"pl-val",
"print-ind",
rsv,
"rsv-inc",
"sl-val",
wgp

FROM PUB.invanal

where analysis-cat not in('d','c','f');

The reason id the function (not match) is a function of qlikview and not the SQL.

Here you are using the SQL query to get the data from database, so you must use the SQL functions instead of QlikView.

Regards,

Kaushik Solanki

Re: Load Script, using where not match

bbi_mba_76 — Mon, 11 Nov 2013 10:54:18 GMT

Hi,

are you using the qlikview match function in a sql select?

Re: Load Script, using where not match

ljackson — Mon, 11 Nov 2013 10:57:35 GMT

Thank you - I couldn't see the wood for the trees!

It's working perfectly now.

Re: Load Script, using where not match

Tue, 17 Dec 2013 16:33:51 GMT

Hi Kaushik,

I used this similar where condition in QVD generator to fetch data from a table. But I noticed that it is also excluding the records with Null Value also.

Could you tell how could this be avoided?