Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
Valued Contributor

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

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
Honored Contributor III

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

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
Honored Contributor III

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

Hi Rachel,

Please try this:

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

Thanks,

AS

pradeepreddy
Valued Contributor II

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

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

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

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
Valued Contributor II

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

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
Honored Contributor III

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

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

antoniotiman
Honored Contributor III

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

If You have MSSqlServer  < 2008 You try

DATEPART(year,date)

Not applicable

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

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

Community Browser