Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
SatyaPaleti
Creator III
Creator III

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

Labels (1)
6 Replies
ignacio_pena
Contributor III
Contributor III

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,

 

 

SatyaPaleti
Creator III
Creator III
Author

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

jwang2016
Partner
Partner

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
Creator III
Creator III
Author

Hi,

 

Thanks for your responce. Year field is not available

 

Thanks and Regards,

Satya

jwang2016
Partner
Partner

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

mikecrengland
Creator III
Creator III

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

 

mike