Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
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,

 

 

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

Highlighted
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