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

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

Mon, 02 Jun 2014 03:33:11 GMT

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.

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

eduardo_sommer — Mon, 02 Jun 2014 04:02:30 GMT

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

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

anbu1984 — Mon, 02 Jun 2014 04:22:25 GMT

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;

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

amit_saini — Mon, 02 Jun 2014 04:44:13 GMT

Hi Rachel,

Please try this:

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

Thanks,

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

PradeepReddy — Mon, 02 Jun 2014 06:44:13 GMT

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

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

Mon, 02 Jun 2014 07:01:57 GMT

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...

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

PradeepReddy — Mon, 02 Jun 2014 07:58:47 GMT

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';

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

anbu1984 — Mon, 02 Jun 2014 08:01:58 GMT

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

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

antoniotiman — Mon, 02 Jun 2014 08:16:49 GMT

If You have MSSqlServer < 2008 You try

DATEPART(year,date)

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

Mon, 02 Jun 2014 09:07:20 GMT

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