Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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:
Are you getting different results for the flag value?
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;
Nop, i tried that but it's not working.
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:
Are you getting different results for the flag value?