Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Problems using Max() in Where clause

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

 

Labels (2)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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

View solution in original post

3 Replies
Highlighted
Partner
Partner

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

View solution in original post

Highlighted
Partner
Partner

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

Work smarter, not harder
Highlighted
Contributor II
Contributor II

Thanks! That did the trick