topic Re: Load last 3 years Data from Sql Select in QlikView

Load last 3 years Data from Sql Select

SatyaPaleti — Sat, 16 Nov 2024 20:53:54 GMT

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

Re: Load last 3 years Data from Sql Select

ignacio_pena — Fri, 10 May 2019 11:57:23 GMT

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,

Re: Load last 3 years Data from Sql Select

SatyaPaleti — Fri, 10 May 2019 13:20:55 GMT

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

Re: Load last 3 years Data from Sql Select

jwang2016 — Fri, 10 May 2019 13:27:01 GMT

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)

Re: Load last 3 years Data from Sql Select

SatyaPaleti — Fri, 10 May 2019 13:31:18 GMT

Hi,

Thanks for your responce. Year field is not available

Thanks and Regards,

Satya

Re: Load last 3 years Data from Sql Select

jwang2016 — Fri, 10 May 2019 14:28:08 GMT

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

Re: Load last 3 years Data from Sql Select

mikecrengland — Fri, 10 May 2019 16:46:12 GMT

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

mike