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

[#1]

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.

[eduardo_sommer]

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]

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]

Hi Rachel,

Please try this:

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

Thanks,

AS

[PradeepReddy]

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

[#6]

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]

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]

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

[antoniotiman]

If You have MSSqlServer  < 2008 You try

DATEPART(year,date)

[#10]

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