Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

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

;

View solution in original post

7 Replies
petter
Partner - Champion III
Partner - Champion III

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

petter
Partner - Champion III
Partner - Champion III

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

;

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

BR,

Lawrence

petter
Partner - Champion III
Partner - Champion III

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

Anonymous
Not applicable
Author

Hi,

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

BR,

Lawrence

Anonymous
Not applicable
Author

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