Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
BryanFontes
Contributor II
Contributor II

Min Value for a date after a Date

Hello guys,

 

I need to find a way to take the minimum date in a column by a group where there are lots of invalid dates (like 0000/04). So I thought of using some date (like 1900/01) as a parameter, and take the minimum value after that. Do you have any idea how could it be done?

As an example, my script is:

LOAD

  ID
  DATE(MIN(Mydate),'YYYY/MM') AS MinDate
From [] Group by ID;

But it returns some invalid dates.

Labels (1)
1 Solution

Accepted Solutions
BryanFontes
Contributor II
Contributor II
Author

Thanks for the quick reply!

 

I solved this problem by filtering the date field before the 'groupedby' table, with:

  • IF(MyDate>DummyDate, <MyDate>,NULL()) AS MyDate

Best Regards.

View solution in original post

2 Replies
stevejoyce
Specialist II
Specialist II

date(<field>) will return null if it's an invalid date.  so this should work:

LOAD

  ID
  DATE(MIN(Date(Mydate)),'YYYY/MM') AS MinDate
From [] Group by ID;

 

if you already replaced invalid dates with a dummy date, then add that to where clause

LOAD

  ID
  DATE(MIN(Date(Mydate)),'YYYY/MM') AS MinDate
From []

where Mydate <> 'dummydate'

Group by ID;

 

BryanFontes
Contributor II
Contributor II
Author

Thanks for the quick reply!

 

I solved this problem by filtering the date field before the 'groupedby' table, with:

  • IF(MyDate>DummyDate, <MyDate>,NULL()) AS MyDate

Best Regards.