Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
danimelo1
Creator
Creator

Create a flag based on an aggregate value

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.

 

Employee.png

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.

Labels (3)
1 Solution

Accepted Solutions
jwjackso
Specialist III
Specialist III

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:

ResultsResults

 

Are you getting different results for the flag value?

View solution in original post

3 Replies
jwjackso
Specialist III
Specialist III

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
Creator
Creator
Author

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

jwjackso
Specialist III
Specialist III

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:

ResultsResults

 

Are you getting different results for the flag value?