topic Re: Fetch data from database for a particular year in QlikView

Fetch data from database for a particular year

Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I wanted to fetch the data from a database table named "table1" which were reported in year 2016

that is from 1st Jan,2016 to till date.

For this ,I am using the below query:

Select * From "table1";

where to_char("Reported_Date",'YYYY')='2016';

But I am getting an error like column not found whenever i put anything (any Function like to_date(), to_char(), Trunc()) before column Name.

Regards,

Anjali Gupta

Re: Fetch data from database for a particular year

nilapril2010 — Tue, 05 Jul 2016 14:07:48 GMT

Try with the following logic -

Select * From Table1

Where Date(Date#(Reported_Date),'YYYY')='2016';

Regards

Nilanjan

Re: Fetch data from database for a particular year

Wed, 06 Jul 2016 06:57:32 GMT

Hi Nilanjan,

I am still facing the same issue.

Regards,

Anjali Gupta

Re: Fetch data from database for a particular year

Wed, 06 Jul 2016 07:12:42 GMT

Try:

Select * From Table1

Where year(YourDateField)=year(yearstart(today()));

Re: Fetch data from database for a particular year

Wed, 06 Jul 2016 07:23:12 GMT

Hi Pravesh,

I tried this also, Any function before the dateField is showing me column not found error.

Regards,

Anjali Gupta

Re: Fetch data from database for a particular year

Anonymous — Wed, 06 Jul 2016 07:27:29 GMT

Hi Anjali,

you need to write the column name exactlly, think that QlikView is case sensitive.

Then try this:

LOAD *

WHERE year(Reported_Date)=year(today());

SQL SELECT * FROM Table1;

Regards!!

Re: Fetch data from database for a particular year

Wed, 06 Jul 2016 07:29:14 GMT

Have a look at your source data using a debug load (limit 100) to check the column formatting of the date field in question.

Re: Fetch data from database for a particular year

tyagishaila — Wed, 06 Jul 2016 07:54:39 GMT

TAB:

Load *,Date(Date#(Reporting_Date,'YYYY')) AS Year;

SQL Select * from table1;

Load * Resident TAB where Year = '2016';

Drop Table TAB;