Create a flag based on an aggregate value

[danimelo1]

Hi experts,

I want a flag to see if an employee is active or not. An employee is active if in the max date there is a '3' in the S column. The problem is that in some rows, an employee has the vale '3' or '0' in the S column and the same date.

I.E: User 8, 9 and 13 are active users, but 90 shouldn't be.

This is the script :

[Actions]:
LOAD 
	[Person ID] as "User Sys ID",
	[Pers.No.],
	Date([Start Date] ) AS [Start Date],
	Date([End Date] ) AS [End Date],
        Year([Start Date]) as "Year",
        Month([Start Date]) as "Month",
	[Act.],
	[ActR] as [Action Reason],
	[S],
	[Position],
	[CoCd],
	[PA],
// 	[PSubarea],
	[PSA]
 FROM [lib://qlikid_danimelo1/Actions.XLSX]
(ooxml, embedded labels, table is Sheet1);
Left Join

//[Termination Action Reasons]:
LOAD [Type] as [Action Reason],
	[Termination Reason],
	[Voluntary/involuntary]
 FROM [lib://qlikid_danimelo1/Actions and reasons.XLSX]
(ooxml, embedded labels, table is [Termination Action Reasons]);

[EMax]:
Load
    "User Sys ID" as "E.User",
    Date(Max("Start Date")) as "FechaMax",
    if(S = 3, 'Activo') as "flag"    
    Resident Actions
    Group By "User Sys ID", S;   
    
Final:
Load 
      "E.User" as "User Sys ID",	 
     "flag" as "Flag"
resident EMax
Where "flag"='Activo';

join (Actions)
Load "User Sys ID",
	 "Flag"
Resident Final;

Drop Tables Final, EMax;

I'm attaching an Excel file with the data. Any idea what I'm missing?

Thanks.

[jwjackso]

How about this:

[EMax]:
Load
"User Sys ID" as "E.User",
Date(Max("Start Date")) as "FechaMax"
Resident Actions Group By "User Sys ID";
Inner Join
Load
"User Sys ID" as "E.User",
"Start Date" as "FechaMax",
S
Resident Actions;

Final:
Load
"E.User" as "User Sys ID",
'Activo' as "Flag"
resident EMax
Where S = 3;

[danimelo1]

Nop, i tried that but it's not working.

[jwjackso · Accepted solution]

Below is the load script that I tested:

Actions:
LOAD * Inline [
"User Sys ID","Start Date",S
8,1/24/2014,0
8,1/24/2014,3
9,7/1/2013,3
9,6/12/2016,1
9,12/10/2016,3
13,5/1/2014,3
13,7/1/2015,3
13,11/1/2015,3
13,11/25/2015,3
13,7/1/2016,0
13,7/1/2016,3
90,1/1/2014,3
90,4/10/2017,0
];

[EMax]:
Load
"User Sys ID" as "E.User",
Date(Max([Start Date])) as FechaMax
Resident Actions
Group By "User Sys ID";
Inner Join
LOAD "User Sys ID" as "E.User",
[Start Date] as FechaMax,
S
Resident Actions;

Final:
Load
"E.User" as "User Sys ID",
'Activo' as "Flag"
resident EMax
Where S = 3;

drop Table Actions;

The results are below:

Results

Are you getting different results for the flag value?