Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'));
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
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
Hi,
are you using the qlikview match function in a sql select?
Thank you - I couldn't see the wood for the trees!
It's working perfectly now.
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?