Create flag against employees that change cost centre by month

[zakpullen]

Hi,

I need to count employees that move cost centre by month. I thought a flag in the script would allow me to do a set analysis for this purpose. In the below table, one employee (yellow highlight) has changed cost centres. I thought a concatenation of Cost Centre and EmployeeID and something like EXISTS or PEEK might do it, but I can't figure it out. Is this possible?

Many thanks

[marcus_sommer]

Such aggregations like count([Cost Centre]) would be working - but it would always be relating to group by fields and the included periods. If it always goes against a fixed state and/or you may outsource n views of them within an extra table it might be suitable for your scenario.

Another approach could be to use interrecord-functions within an appropriate sorted resident load querying the previous record for the employee and cost centre and comparing it with the current record - enabling you to create a 0/1 flag and/or any running field.

[Daniel_Castella · Accepted solution]

Hi @zakpullen 

I think the easiest way to do it is using mappings. Take a look on the code below. It returns the Flag as 1 for the Employee that changes the Cost Center and 0 for the ones that didn't (I also put the flag as null for the first month to avoid confusion). 

Main:

LOAD Date(Date#(Date,'DD/MM/YYYY'),'YYYYMM') as Date, Cost_Center, Employee_ID

INLINE [

Date, Cost_Center, Employee_ID

30/09/2025, CC1, E001

30/09/2025, CC1, E002

30/09/2025, CC2, E003

30/09/2025, CC2, E004

30/09/2025, CC3, E005

30/09/2025, CC3, E006

30/09/2025, CC4, E007

31/10/2025, CC1, E001

31/10/2025, CC1, E002

31/10/2025, CC2, E003

31/10/2025, CC2, E004

31/10/2025, CC3, E005

31/10/2025, CC3, E006

31/10/2025, CC1, E007

];

Map:

Mapping

LOAD Date(AddMonths(Date,1),'YYYYMM')&Cost_Center&Employee_ID,

0 as Flag

RESIDENT Main;

Map2:

Mapping

LOAD Date(min(Date),'YYYYMM')&Employee_ID,

'-' as Flag

RESIDENT Main

GROUP BY Employee_ID;

Final:

LOAD Date,

Cost_Center,

     Employee_ID,

     Applymap('Map',Date&Cost_Center&Employee_ID,Applymap('Map2',Date&Employee_ID,'1')) as Flag

RESIDENT Main;

DROP TABLE Main;

Let me know if it works for you. 

Kind Regards

Daniel

[zakpullen]

Hi Daniel,

I had to remove Date# from the expression, but it seems to have worked.

Thank you so much.

[Daniel_Castella]

Hi @zakpullen 

Yes, the Date# is set in my code because I'm loading the dates from an Inline. Then, they are considered a string and I need to put the Date# to transform them to a date. However, if your code provides the data already in date format, then it is not needed.

Kind Regards

Daniel