Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

satyapaleti
Contributor II

Load last 3 years Data from Sql Select

Hi Friends,

I am loading data from SQL. 

Sql file contains data from 2004 but i need to load only last 3 years data. How can we do it?

Here is the script I am using

Test:

load 

CUSTID    as   [Customer Id],

CNAME    as    [Customer Name]

CJD           as    [Customer Join Date] ;     CJD is in MM/DD/YYYY format

SQL Select CUSTID  CNAME  CJD  from CUST

Where     year(CJD) > ='2017';         

When I am loading data it's throwing an error.  Could some can guide me How to fetch last 3 years data in this case

 

Thanks and Regards,

Satya

6 Replies
ignacio_pena
New Contributor III

Re: Load last 3 years Data from Sql Select

Hello Satya,

I think this script should work:

Test:

load 

CUSTID    as   [Customer Id],

CNAME    as    [Customer Name]

CJD           as    [Customer Join Date] ;    

SQL Select 

CUSTID,  CNAME,  CJD 

from CUST

Where     TO_DATE(CJD, 'MM/DD/YYYY') > ='01/01/2017'; 

Another option in the SQL where is:

SQL Select 

CUSTID,  CNAME,  CJD 

from CUST

Where    EXTRACT(YEAR FROM CJD) = '2017';

Regards,

 

 

Highlighted
satyapaleti
Contributor II

Re: Load last 3 years Data from Sql Select

Hi,

Thank you so much for your response.  I have used both of your queries but still code is not getting executed. Following error I am facing

 

/QTQVC/OPEN_STREAM failed after 00:00:00 Key = SQL_ERROR (ID:00 Type:E Number:001 Comma without preceding colon (after OPEN ?).)

 

Thanks and Regards,

Satya

Partner
Partner

Re: Load last 3 years Data from Sql Select

you can use the code below to load only last 3 years data from the table.

select * from table_name where field_year in (select distinct field_year from table_name order by 1 desc limit 3)

satyapaleti
Contributor II

Re: Load last 3 years Data from Sql Select

Hi,

 

Thanks for your responce. Year field is not available

 

Thanks and Regards,

Satya

Partner
Partner

Re: Load last 3 years Data from Sql Select

get the year from the field that contains the year info.

mikecrengland
Contributor III

Re: Load last 3 years Data from Sql Select

Is CJD a date data type? you may need to convert it.

 

mike