Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
saumyashah90
Specialist
Specialist

Set analysis help Expression

AsOfDateEmployeeIDTeamCodeRegionDepartmentTeam
2020-05-311111012CSouthRetail ADigital A
2020-05-312222132VNorthRetail BDigital B
2020-06-302222472HEastRetail CDigital C
2020-06-303333475UEastRetail CDigital F
2020-06-304444413NWestRetail DDigital G
2020-07-312222763IEastRetail DDigital H
2020-07-315555472HEastRetail CDigital C
2020-07-304444472HEastRetail CDigital C

 

This is the employee data, i want create headcount analysis.

I cannot do anything in the script. All data will just get appended anymonth.

User will select 2 months for comparison and 

They want get  New to the company, Retained , Resigned from the company, Internal transfer

this should be valid based on selection of Region, Department and Team

So when i compare June and July Month i  select  only Team (Digital C ), I should get output

New to Bank: 1 (Employee 5555)

Resigned from Bank: 0

Internal Transfer: + 1 (Employee 4444)

Internal Transfer: -1(Employee 2222)

 

So when i compare June and July Month i  select only Region (East) , I should get output

New to Bank: 1 (Employee 5555)
Resigned from Bank: 1 (Employee 3333)
Internal Transfer: +1 ( Employee 4444 )
Internal Transfer: -0

Note: Any two months can be selected for comparison, Jan Mar, Jan Nov, Feb Apr, June Nov, etc

Hint: Lowest grain is Team Code, If team code of employee is present and not present in last month , he is new to the bank,
if team code of employee is not present this month but present in last month, his has resigned

if team code is present for both month but different and it is same as filter selection, +ve internal transfer,

if team code is present for both month but different and it is  not same as filter selection, -ve internal transfer

 

I have a composite key of EmployeeID,Teamcode at the backend if useful

Labels (2)
22 Replies
Saravanan_Desingh

Questions:

1111 - Why it is not been considered as Resigned?

2222 - TeamCode and Team are different. How you say it is +ve internal transfer

saumyashah90
Specialist
Specialist
Author

Question ->1111 - Why it is not been considered as Resigned? : 

Reply->

Because i have taken June July are comparing month.
At a time two months will be there for comparison.

If we would have compared May June or May July, 1111 would be resigned.

 

Question->2222 - TeamCode and Team are different. How you say it is +ve internal transfer

1st condition - he should not be totaly new to company, he/she should have some Teamcode tagged in previous month.

2nd condition - Team code should be different comparing two month

3rd condition - which ever teamcode and its related team is selected , if  team code of current(higher month) is same its +ve transfer

 

Saravanan_Desingh

I am still not clear.

2222:
2020-05-31 2222 132V North Retail B Digital B
2020-06-30 2222 472H East Retail C Digital C
2020-07-31 2222 763I West Retail D Digital H
4444:
2020-06-30 4444 413N West Retail D Digital G
2020-07-30 4444 445B West Retail D Digital E

With the above sample, please tell me where you are seeing as same team and which one is not?

sunny_talwar

I am also not clear on Internal transfers, but the first two could be

='New to Bank: ' &
Count(DISTINCT {<EmployeeID = P({<AsOfMonthYear = {"$(=Max({<Team>}AsOfMonthYear))"}, Team>})*e({<AsOfMonthYear = {"$(=Min({<Team>}AsOfMonthYear))"}, Team>}), Team>} EmployeeID)
& '(' &
Concat(DISTINCT {<EmployeeID = P({<AsOfMonthYear = {"$(=Max({<Team>}AsOfMonthYear))"}, Team>})*e({<AsOfMonthYear = {"$(=Min({<Team>}AsOfMonthYear))"}, Team>}), Team>} EmployeeID, ',')
&')'

 

='Resigned: ' &
Count(DISTINCT {<EmployeeID = e({<AsOfMonthYear = {"$(=Max({<Team>}AsOfMonthYear))"}, Team>})*p({<AsOfMonthYear = {"$(=Min({<Team>}AsOfMonthYear))"}, Team>}), Team>} EmployeeID)
& '(' &
Concat(DISTINCT {<EmployeeID = e({<AsOfMonthYear = {"$(=Max({<Team>}AsOfMonthYear))"}, Team>})*p({<AsOfMonthYear = {"$(=Min({<Team>}AsOfMonthYear))"}, Team>}), Team>} EmployeeID, ',')
&')'

 

Saravanan_Desingh

Resigned and New are captured in this code.

SET DateFormat='YYYY-MM-DD';

tab1:
LOAD * INLINE [
    AsOfDate, EmployeeID, TeamCode, Region, Department, Team
    2020-05-31, 1111, 012C, South, Retail A, Digital A
    2020-05-31, 2222, 132V, North, Retail B, Digital B
    2020-06-30, 2222, 472H, East, Retail C, Digital C
    2020-06-30, 3333, 475U, East, Retail C, Digital F
    2020-06-30, 4444, 413N, West, Retail D, Digital G
    2020-07-31, 2222, 763I, West, Retail D, Digital H
    2020-07-31, 5555, 472H, North, Retail B, Digital C
    2020-07-31, 4444, 445B, West, Retail D, Digital E
];

Left Join(tab1)
LOAD Date(Max(AsOfDate)) As CurrMonth, Date(Max(AsOfDate,2)) As PrevMonth
Resident tab1
;
Let vCurrMonth=Peek('CurrMonth');
Let vPrevMonth=Peek('PrevMonth');

Left Join(tab1)
LOAD EmployeeID, 
	 Min(AsOfDate='$(vCurrMonth)') As F3, Min(AsOfDate='$(vPrevMonth)') As F4
Resident tab1
Where Match(AsOfDate,'$(vCurrMonth)','$(vPrevMonth)')
Group By EmployeeID
;
Left Join(tab1)
LOAD *, If(F3<F4, 'New',If(F3>F4, 'Resigned')) As Status
Resident tab1;
Saravanan_Desingh

Output.

commQV45.PNG

Saravanan_Desingh

If I understand on the Internal Transfer logic,

SET DateFormat='YYYY-MM-DD';

tab1:
LOAD * INLINE [
    AsOfDate, EmployeeID, TeamCode, Region, Department, Team
    2020-05-31, 1111, 012C, South, Retail A, Digital A
    2020-05-31, 2222, 132V, North, Retail B, Digital B
    2020-06-30, 2222, 472H, East, Retail C, Digital C
    2020-06-30, 3333, 475U, East, Retail C, Digital F
    2020-06-30, 4444, 413N, West, Retail D, Digital G
    2020-07-31, 2222, 763I, West, Retail D, Digital H
    2020-07-31, 5555, 472H, North, Retail B, Digital C
    2020-07-31, 4444, 445B, West, Retail D, Digital E
];

Left Join(tab1)
LOAD Date(Max(AsOfDate)) As CurrMonth, Date(Max(AsOfDate,2)) As PrevMonth
Resident tab1
;
Let vCurrMonth=Peek('CurrMonth');
Let vPrevMonth=Peek('PrevMonth');

Left Join(tab1)
LOAD EmployeeID, Count(DISTINCT Department) As F1,
	 Min(AsOfDate='$(vCurrMonth)') As F3, Min(AsOfDate='$(vPrevMonth)') As F4
Resident tab1
Where Match(AsOfDate,'$(vCurrMonth)','$(vPrevMonth)')
Group By EmployeeID
;
Left Join(tab1)
LOAD *, If(F3<F4, 'New',If(F3>F4, 'Resigned', If(F1>1 And AsOfDate='$(vCurrMonth)','+ve Internal Transfer','-ve Internal Transfer'))) As Status
Resident tab1
Where Match(AsOfDate,'$(vCurrMonth)','$(vPrevMonth)');
Saravanan_Desingh

A bug was fixed in this version.

SET DateFormat='YYYY-MM-DD';

tab1:
LOAD * INLINE [
    AsOfDate, EmployeeID, TeamCode, Region, Department, Team
    2020-05-31, 1111, 012C, South, Retail A, Digital A
    2020-05-31, 2222, 132V, North, Retail B, Digital B
    2020-06-30, 2222, 472H, East, Retail C, Digital C
    2020-06-30, 3333, 475U, East, Retail C, Digital F
    2020-06-30, 4444, 413N, West, Retail D, Digital G
    2020-07-31, 2222, 763I, West, Retail D, Digital H
    2020-07-31, 5555, 472H, North, Retail B, Digital C
    2020-07-31, 4444, 445B, West, Retail D, Digital E
];

Left Join(tab1)
LOAD Date(Max(AsOfDate)) As CurrMonth, Date(Max(AsOfDate,2)) As PrevMonth
Resident tab1
;
Let vCurrMonth=Peek('CurrMonth');
Let vPrevMonth=Peek('PrevMonth');

Left Join(tab1)
LOAD EmployeeID, Count(DISTINCT Department) As F1,
	 Min(AsOfDate='$(vCurrMonth)') As F3, Min(AsOfDate='$(vPrevMonth)') As F4
Resident tab1
Where Match(AsOfDate,'$(vCurrMonth)','$(vPrevMonth)')
Group By EmployeeID
;
Left Join(tab1)
LOAD *, If(F3<F4, 'New',If(F3>F4, 'Resigned', If(AsOfDate='$(vCurrMonth)',If(F1>1,'+ve Internal Transfer','-ve Internal Transfer')))) As Status
Resident tab1
Where Match(AsOfDate,'$(vCurrMonth)','$(vPrevMonth)');
Saravanan_Desingh

Output.

commQV46.PNG