Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

yanivbm88
Contributor

SQL WHERE condition by Year (In Load Script)

Hi,

I am uploading data through ODBC connection, with SQL SELECT statement.

I want to add a WHERE clause by year (Year >=2017).

My dates are formatted as string ('DD.MM.YY'). Therefore I will probably need to use 'Right' function (But I'm open to other ideas as well).

What will be a valid syntax for this condition?

I repeat, I am not referring to an upload from a Qvd file.

Thanks in advance.

8 Replies
sibusiso90
Contributor III

Re: SQL WHERE condition by Year (In Load Script)

Load *

SQL

Select

     Top 10

     *

From

     MyTable

Where

     Right(fieldname,2) >= '17';

or you can use preseeding load if your table is not too big.

sibusiso90
Contributor III

Re: SQL WHERE condition by Year (In Load Script)

With preseeding load you will bring in all your data then limit it within QlikView.

LOAD

     *

Where Right(FieldName,2)<17;

SQL

Select *

from mytable;

yanivbm88
Contributor

Re: SQL WHERE condition by Year (In Load Script)

Hi Sibusiso,

Below is my script. I get a 'Connector Reply Error'.

ReferenceLines:

SQL SELECT *

FROM "SCM_Reference_Data"."Lines"

WHERE Right(CreationDate,2)>='17';

If I upload  the exact same script (below) without the WHERE statement, it works fine.

ReferenceLines:

SQL SELECT *

FROM "SCM_Reference_Data"."Lines";

sibusiso90
Contributor III

Re: SQL WHERE condition by Year (In Load Script)

Then let's use the second option,

Load

     *

where right(CreationDate,2)>=17;

SQL SELECT *

FROM "SCM_Reference_Data"."Lines";

yanivbm88
Contributor

Re: SQL WHERE condition by Year (In Load Script)

My table is very big, and I want to avoid uploading it all for upload time reducing purposes

yanivbm88
Contributor

Re: SQL WHERE condition by Year (In Load Script)

My table is very big, and I want to avoid uploading it as a whole for time reducing purposes

sibusiso90
Contributor III

Re: SQL WHERE condition by Year (In Load Script)

I haven't seen the error and I can't replicate it on my machine. With my data, it works with the same syntax.

This is what I used to try and replicate your problem still can't get your error.

Ref:

sql

SELECT [IssueID]

      ,[InstanceID]

      ,[StartDate]

      ,[Origin]

  FROM [HelpDesk].[dbo].[hdIssues]

  where left(StartDate,4) >='2015';

amartinez35
Valued Contributor

Re: SQL WHERE condition by Year (In Load Script)

With SQL Server :

SQL

   Select

     *

   FROM myTable

   WHERE YEAR(myDate) >= 2017

Oracle :

SQL

   Select

     *

   FROM myTable

WHERE EXTRACT(YEAR FROM myDate) >= 2017

Community Browser