Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
i am loading data from MS SQL and i want to filter certain fields without any aggregate. I have not been able to find a solution.
Apparently "where" function does not exist in qliksense if i am not mistaken.
So, i have a field with values of "AX1" " AX2" and "AX3". I want to filter my data and load only for "AX1".
How do i do it?
I know i can bypass with "if" and then hide null values but i do not find this efficient. I want to load only the necessary data.
Please help me on the which function i should use but also the syntax and where in the script of data load editor i should put the command.
Thank you all in advance,
Lawrence
A load script could look like this:
LOAD
*
;
SQL
SELECT
order_no,
order_date,
amount,
customer_no
FROM
orders
WHERE
region = 'AX1'
;
By putting the WHERE into the SQL's SELECT statement you filter away rows at the earliest possible stage. It is also possible to filter in the LOAD statement but then all the rows has to be sent to Qlik before the filtering takes place which will normally be very inefficient and slow.
LOAD
*
WHERE
region = 'AX1'
;
SQL
SELECT
order_no,
order_date,
amount,
customer_no,
region
FROM
orders
;
WHERE is available both in Qlik Sense and also in the SQL Server query where you should place it.
A load script could look like this:
LOAD
*
;
SQL
SELECT
order_no,
order_date,
amount,
customer_no
FROM
orders
WHERE
region = 'AX1'
;
By putting the WHERE into the SQL's SELECT statement you filter away rows at the earliest possible stage. It is also possible to filter in the LOAD statement but then all the rows has to be sent to Qlik before the filtering takes place which will normally be very inefficient and slow.
LOAD
*
WHERE
region = 'AX1'
;
SQL
SELECT
order_no,
order_date,
amount,
customer_no,
region
FROM
orders
;
Hi Petter, thanks for the reply.
I added the where but i get a "connector reply error" message.
SELECT SALESID,
SALESNAME,
...
ASOTD1S,
ASPROMPT,
ASNEGATIVEQUANTITY,
DATAAREAID,
ASTIMESTARTEDFIRSTCONFIRMED,
ASTIMEOFSTARTED,
SALESTAKER,
ASTIMESTARTEDSTATUS6
FROM "AXAPTA_LIVE".dbo."ASKPI_SALESORDER"
where DATAAREID='AX1';
This is the script. What is wrong this that?
Many thanks,
Lawrence
HI, i am sorry but i can not make it work in either positions. I get the same message. "connector reply error"
BR,
Lawrence
Are you able to run the same SELECT statement in Microsoft SQL Server Management Studio without any errors?
Hi,
i have found the solution. It was a typo error at the end. Thanks for your help.
BR,
Lawrence
Hi,
I had similar issue. Try script without where, just to confirm it is working. If it is, try it with this one or similar, you'll need to experiment a little
FROM dbo.ASKPI_SALESORDER
where DATAAREID='AX1';
It may be needed to strip out " or you may need to have them, I do not know, but I believe that it is ODBC settings issue.
I had similar one, SQL that worked correct did not work in QlikView, but when I removed " it worked. Maybe you'll have to remove dbo. part also
mine is like this:
FROM MAITXInvoiceSaleLine LinijaRacuna where szDate>=$(vDatum) and szDate<$(vDatumKraj);