Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

lavrendis
New Contributor

Data load filter

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

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Data load filter

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

;

7 Replies
Highlighted
MVP
MVP

Re: Data load filter

WHERE is available both in Qlik Sense and also in the SQL Server query where you should place it.

MVP
MVP

Re: Data load filter

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

;

lavrendis
New Contributor

Re: Data load filter

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

lavrendis
New Contributor

Re: Data load filter

HI, i am sorry but i can not make it work in either positions. I get the same message. "connector reply error"

BR,

Lawrence

MVP
MVP

Re: Data load filter

Are you able to run the same SELECT statement in Microsoft SQL Server Management Studio without any errors?

lavrendis
New Contributor

Re: Data load filter

Hi,

i have found the solution. It was a typo error at the end. Thanks for your help.

BR,

Lawrence

shadun76
New Contributor II

Re: Data load filter

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

Community Browser