Min Value for a date after a Date

[BryanFontes]

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.

[stevejoyce]

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 · Accepted solution]

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.