Flagging most recent date with an IF condition

[Jonathan_Svoboda]

Hello,

I am trying to create a field that flags the most recent training enrollment date only if the product equals 'new". I managed to create the flag field without an issue but I cannot seem to find a way to add the training product = new condition.

LOAD
"Training Code",
max("Training Enrollment Date Key") as "Training Enrollment Date Key",
1 as "Most Recent Enrollment"

FROM [xxx.qvd]

Group By "Training Code";

The problem is, when I try to add an if statement to include the condition I want, I get an error. See below for what I tried to do, if someone could tell me where I went wrong here that would be appreciated.

Left Join

LOAD
"Training Code",
max("Training Enrollment Date Key") as "Training Enrollment Date Key",
if("Training Product" = 'New',1,0) as "Most Recent Enrollment"

FROM [xxx.qvd]

Group By "Training Code";

[QFabian]

Hi @Jonathan_Svoboda , please check something like this :

Data:
Load
"Training Code",
"Training Enrollment Date Key",
"Training Product"
FROM [xxx.qvd];

left join
Load
"Training Code",
max("Training Enrollment Date Key") as "Training Enrollment Date Key",

'Most Recent Enrollment'  as flag
Resident Data
Where "Training Product" = 'New'
group by
"Training Code";