Load Script, using where not match

[ljackson]

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'));

[kaushiknsolanki · Accepted solution]

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

[bbi_mba_76]

Hi,

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

[ljackson]

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

It's working perfectly now.

[#5]

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?