Discussion Board for collaboration on QlikView Scripting.
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.
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.
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;
Please try this:
1) Extract records only from the year 2012
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?
Hi, apparently i cant use year(Field) = 2012. this is what i did:
from <filename> where year(statusdate)=2012;
an error message stated that 'year' is an invalid identifier...
If you are fetching data from file, use the bellow..
(ooxml, embedded labels, table is Sheet1)
Qlikview Year function may not be available in database. Which database are you using?
If You have MSSqlServer < 2008 You try
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')))