Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to extract particular year and find the column with the earliest date?

Hi, how do I extract records only from the year 2012? I have many date fields all with different dates and I also want to find the date field with the earliest date. How can I do this? Thanks.

9 Replies
eduardo_sommer
Partner - Specialist
Partner - Specialist

Hi, Rachel

You can use a where clause, in your Load command with year(Field) = 2012.

For the second question, you can use the min function combined with the if function.

Eduardo

anbu1984
Master III
Master III

Try like this

Load If(DateField1 < DateField2 , If(DateField1 < DateField3, 'DateField1 has minimum date','DateField3 has minimum date'),if(DateField2 <DateField3, 'DateField2 has minimum date','DateField3 has minimum date'));

Load min(DateField1) as DateField1 ,min(DateField2) as DateField2,min(DateField3) as DateField3 From Table where Year(DateField)=2012;

amit_saini
Master III
Master III

Hi Rachel,

Please try this:

=if(match(Year,,'2012' ),Year)

Thanks,

AS

PradeepReddy
Specialist II
Specialist II

1) Extract records only from the year 2012

     Load *;

     SQL SELECT *

     FROM Table_Name where Year(Date_Field)='2012' ;

2)  For the second question, i.e identifying the date field which has minimum value. I have one doubt

     - Do you want this at Script Level/UI part of the report?

Regards

Pradeep

Not applicable
Author

Hi, apparently i cant use year(Field) = 2012. this is what i did:

sql select

.

.(other fields)

.

statusdate.

.

.

from <filename> where year(statusdate)=2012;

an error message stated that 'year' is an invalid identifier...

PradeepReddy
Specialist II
Specialist II

If you are fetching data from file, use the bellow..

LOAD *

FROM

File_Name.xlsx

(ooxml, embedded labels, table is Sheet1)

where Year(Status_Dt)='2012';

anbu1984
Master III
Master III

Qlikview Year function may not be available in database. Which database are you using?

antoniotiman
Master III
Master III

If You have MSSqlServer  < 2008 You try

DATEPART(year,date)

Not applicable
Author

Hi Rachel,

1) if your Date field is returning dates from DB in format such as 'DD/MM/YY hh:mm:ss TT' or 'DD/MM/YY' , try this

Year(Date(Trunc(Date_field),'DD/MM/YY') = '2012' or Year(Date(Date_field,'DD/MM/YY') = '2012'

2) Convert the Date field into number and use the min function, Min(num(Date#(Datefield,'format')))

Regards

Nimitha