Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
evie
Contributor II
Contributor II
‎2023-06-20 02:06 PM

Creating a new flag column using two separate flag columns

This is a table of my sample data:

EMPLOYEE_ID NAME SUPERVISOR_ID SUP_NAME PREV_SUP_ID PREV_SUP_NAME DEPARTMENT PREV_DEPT BUS_TITLE SUP_DEPT_CHANGE TITLE_CHANGE EFFECTIVE_START
12345 Jenny 41852 Megan blank - 589 blank - 1 1 1/1/2023
12345 Jenny 41852 Megan blank - 589 blank - 1 1 1/2/2023
12345 Jenny 67890 Manny 41852 Megan 6605 589 Center Rep 1 1 1/3/2023
12345 Jenny 67890 Manny blank - 6605 blank Center Rep 1 1 1/4/2023
23568 Cam 38797 Josh blank - 6605 blank - 0 0 1/1/2023
23568 Cam 38797 Josh blank - 6605 blank - 0 0 1/1/2023
23568 Cam 38797 Josh blank - 6605 blank Center Rep 0 0 1/1/2023
23568 Cam 38797 Josh blank - 6605 blank 1st stage 0 0 1/1/2023
89756 Maddie 41852 Megan blank - 205 blank - 0 1 1/2/2023
89756 Maddie 41852 Megan blank - 205 blank - 0 1 1/3/2023
89756 Maddie 41852 Megan blank - 205 blank - 0 1 1/4/2023
59689 Cy 41852 Megan blank - 205 blank - 1 0 1/5/2023
59689 Cy 48571 Nick 41852 Megan 589 blank - 1 0 1/6/2023
59689 Cy 48571 Nick blank - 589 blank Center Rep 1 0 1/7/2023
57483 Adrian 45648 Nick blank - 589 blank 1st stage 1 0 1/6/2023


SUP_DEPT_CHANGE returns a 1 if the employee has ever changed supervisors or departments and a 0 if they have had no changes. TITLE_CHANGE returns a 1 if they need to update their BUS_TITLE and a 0 if it is updated. I created the flag function for SUP_DEPT_CHANGE and TITLE_CHANGE with this, (not sure if this is useful or relevant):

Table1:
NoConcatenate
LOAD

  EMPLOYEE_ID,

  if(trim(PREVIOUS_SUP_ID) <> '' or trim(PREVIOUS_DEPT) <> '', '1', '0') as SUP_DEPT_CHANGE,

  if(IsNull(BUSINESS_TITLE) or WildMatch(BUSINESS_TITLE, '*Rep*', '*ist*'), '1', '0') as TITLE_CHANGE

Resident Tmp3;


This is what I used to try to create an Updates flag based on SUP_DEPT_CHANGE and TITLE_CHANGE, which wasn't really working:

Load

  EMPLOYEE_ID,

  If(SUP_DEPT_CHANGE= 1 and TITLE_CHANGE= 1, 'Change in Sup/Dept, Title needs updated',

  If(SUP_DEPT_CHANGE= 0 and TITLE_CHANGE= 1, 'No change in Sup/Dept, Title needs updated',

  If(SUP_DEPT_CHANGE= 1 and TITLE_CHANGE= 0, 'Change in Sup/Dept, Title is updated',

  If(SUP_DEPT_CHANGE= 0 and TITLE_CHANGE= 0, 'No change in Sup/Dept, Title is updated')))) as Updates

Resident Table1;


I need the table to look like this:

EMPLOYEE_ID NAME SUPERVISOR_ID SUP_NAME PREV_SUP_ID PREV_SUP_NAME DEPARTMENT PREV_DEPT BUS_TITLE Updates TITLE_CHANGE
12345 Jenny 67890 Manny blank - 6605 blank Center Rep Change in Sup/Dept, Title needs updated  1/4/2023
23568 Cam 38797 Josh blank - 6605 blank 1st stage No change in Sup/Dept, Title is updated 1/1/2023
89756 Maddie 41852 Megan 41852 - 205 blank - No change in Sup/Dept, Title needs updated 1/4/2023
59689 Cy 48571 Nick blank - 589 blank - Change in Sup/Dept, Title is updated  1/7/2023


Where it pulls the latest date for each EMPLOYEE_ID while also returning the correct Updates flag based on an employee's supervisor or department changes in non-displayed rows and their title.

I keep getting 0s in most of the SUP_DEPT_CHANGEs column because my script is only looking at the row with the latest date, not all of them and most of the supervisor or department changes don't happen in the latest row. I am not sure how to get my script to look at those changes and return the correct Updates flag without having to return every date for each employee. 

Can you help with this?

 
Labels (5)
Labels
1,385 Views
0 Likes
7 Replies
Kushal_Chawda
MVP
MVP
‎2023-06-20 06:19 PM

@evie   SUP_DEPT_CHANGE and TITLE_CHANGE logic you have used is working? Where exactly issue is?

1,360 Views
0 Likes
evie
Contributor II
Contributor II
‎2023-06-20 06:59 PM
Author

In response to Kushal_Chawda

0s are returning in almost every SUP_DEPT_CHANGE row regardless of if that employee had a change in supervisor or department previously. I am assuming this is because my script is only looking at the row with the latest date for each employee, not all of them and most of the supervisor or department changes don't happen in the latest row. 

This is what my script is returning in Updates because of this issue:

EMPLOYEE_ID NAME SUPERVISOR_ID SUP_NAME PREV_SUP_ID PREV_SUP_NAME DEPARTMENT PREV_DEPT BUS_TITLE Updates TITLE_CHANGE
12345 Jenny 67890 Manny blank - 6605 blank Center Rep No change in Sup/Dept, Title needs updated 1/4/2023
23568 Cam 38797 Josh blank - 6605 blank 1st stage No change in Sup/Dept, Title is updated 1/1/2023
89756 Maddie 41852 Megan 41852 - 205 blank - No change in Sup/Dept, Title needs updated 1/4/2023
59689 Cy 48571 Nick blank - 589 blank - No change in Sup/Dept, Title is updated 1/7/2023

 

1,354 Views
0 Likes
Kushal_Chawda
MVP
MVP
‎2023-06-20 07:06 PM

@evie  What's your row data and what is expected output?

1,351 Views
0 Likes
evie
Contributor II
Contributor II
‎2023-06-20 07:15 PM
Author

In response to Kushal_Chawda

This is my sample data:

EMPLOYEE_ID NAME SUPERVISOR_ID SUP_NAME PREV_SUP_ID PREV_SUP_NAME DEPARTMENT PREV_DEPT BUS_TITLE SUP_DEPT_CHANGE TITLE_CHANGE EFFECTIVE_START
12345 Jenny 41852 Megan blank - 589 blank - 1 1 1/1/2023
12345 Jenny 41852 Megan blank - 589 blank - 1 1 1/2/2023
12345 Jenny 67890 Manny 41852 Megan 6605 589 Center Rep 1 1 1/3/2023
12345 Jenny 67890 Manny blank - 6605 blank Center Rep 1 1 1/4/2023
23568 Cam 38797 Josh blank - 6605 blank - 0 0 1/1/2023
23568 Cam 38797 Josh blank - 6605 blank - 0 0 1/1/2023
23568 Cam 38797 Josh blank - 6605 blank Center Rep 0 0 1/1/2023
23568 Cam 38797 Josh blank - 6605 blank 1st stage 0 0 1/1/2023
89756 Maddie 41852 Megan blank - 205 blank - 0 1 1/2/2023
89756 Maddie 41852 Megan blank - 205 blank - 0 1 1/3/2023
89756 Maddie 41852 Megan blank - 205 blank - 0 1 1/4/2023
59689 Cy 41852 Megan blank - 205 blank - 1 0 1/5/2023
59689 Cy 48571 Nick 41852 Megan 589 blank - 1 0 1/6/2023
59689 Cy 48571 Nick blank - 589 blank Center Rep 1 0 1/7/2023
57483 Adrian 45648 Nick blank - 589 blank 1st stage 1 0 1/6/2023


This is my expected output:

EMPLOYEE_ID NAME SUPERVISOR_ID SUP_NAME PREV_SUP_ID PREV_SUP_NAME DEPARTMENT PREV_DEPT BUS_TITLE Updates EFFECTIVE_START
12345 Jenny 67890 Manny blank - 6605 blank Center Rep Change in Sup/Dept, Title needs updated  1/4/2023
23568 Cam 38797 Josh blank - 6605 blank 1st stage No change in Sup/Dept, Title is updated 1/1/2023
89756 Maddie 41852 Megan blank - 205 blank - No change in Sup/Dept, Title needs updated 1/4/2023
59689 Cy 48571 Nick blank - 589 blank - Change in Sup/Dept, Title is updated  1/7/2023


So columns SUP_DEPT_CHANGE and TITLE_CHANGE should look like this:

SUP_DEPT_CHANGE TITLE_CHANGE
1 1
0 0
0 1
1 0


But they instead look like this: 

SUP_DEPT_CHANGE TITLE_CHANGE
0 1
0 0
0 1
0 0

which changes the Updates output.

1,349 Views
0 Likes
evie
Contributor II
Contributor II
‎2023-06-20 07:20 PM
Author

In response to evie

I should add, columns SUP_DEPT_CHANGE and TITLE_CHANGE returned the correct value before MAX(EFFECTIVE_START) was added to the script, but I only want that last date for each employee to show.

1,347 Views
0 Likes
Kushal_Chawda
MVP
MVP
‎2023-06-20 07:26 PM

@evie  If I understood correctly try below

 

Table1:
NoConcatenate
LOAD *,
  if(trim(PREVIOUS_SUP_ID) <> '' or trim(PREVIOUS_DEPT) <> '', '1', '0') as SUP_DEPT_CHANGE,
  if(IsNull(BUSINESS_TITLE) or WildMatch(BUSINESS_TITLE, '*Rep*', '*ist*'), '1', '0') as TITLE_CHANGE
Resident Tmp3;

drop Table Tmp3;

Final:
LOAD *,
     If(SUP_DEPT_CHANGE= 1 and TITLE_CHANGE= 1, 'Change in Sup/Dept, Title needs updated',
  If(SUP_DEPT_CHANGE= 0 and TITLE_CHANGE= 1, 'No change in Sup/Dept, Title needs updated',
  If(SUP_DEPT_CHANGE= 1 and TITLE_CHANGE= 0, 'Change in Sup/Dept, Title is updated',
  If(SUP_DEPT_CHANGE= 0 and TITLE_CHANGE= 0, 'No change in Sup/Dept, Title is updated')))) as Updates
Resident Table1;

drop Table Table1;

Inner join(Final)
LOAD EMPLOYEE_ID,
     date(max(EFFECTIVE_START)) as EFFECTIVE_START
Resident Final
Group by EMPLOYEE_ID;

 

1,346 Views
0 Likes
evie
Contributor II
Contributor II
‎2023-06-20 08:46 PM
Author

In response to Kushal_Chawda

I am running into an issue with

date(max(EFFECTIVE_START)) as EFFECTIVE_START

because EFFECTIVE_START is not in Tmp3, but Tmp3 calculates PREVIOUS_SUP_ID and PREVIOUS_DEPT.

Any way you can show me where / how to get this into my script? I have attached it if you're able to help.

Preview file
2 KB
1,335 Views
0 Likes