Problems using Max() in Where clause

[resajdak]

I'm  do a script load where the latest date is pulled from the source using Max(Alert_Date).    Cant seem to make this work.

T2:
LOAD
D_Number,
Alert_Title,
Alert_Date,
Alert_Type,
Resident T1
Where Alert_Type = 'Structured' and Alert_Date = Max(Alert_Date);

[dplr-rn · Accepted solution]

max will not work that way.

you will need to load max into another temporary table

Temp:

LOAD
Max(Alert_Date) as MaxALERTDate,
Resident T1
Where Alert_Type = 'Structured'  ;

use peek to get the max date into a variable

let vtemp=Peek('MaxALERTDate', 0, 'Temp')

and use that variable in the where clause

[JordyWegman]

Hi Resajdak,

You can also try this:

T2:
LOAD
  D_Number,
  Alert_Title,
  Max(Alert_Date) as Alert_Date,
  Alert_Type,
Resident T1
Group by D_Number, Alert_Title, Alert_Type
Where Alert_Type = 'Structured';

Jordy

Climber

[resajdak]

Thanks! That did the trick