Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
lavrendis
Contributor
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

1 Solution

Accepted Solutions
petter
Partner
Partner

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
Partner

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

petter
Partner
Partner

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

lavrendis
Contributor
Contributor
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

lavrendis
Contributor
Contributor
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
Partner

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

lavrendis
Contributor
Contributor
Author

Hi,

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

BR,

Lawrence

shadun76
Contributor II
Contributor II

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