topic Re: Create flag against employees that change cost centre by month in App Development

Create flag against employees that change cost centre by month

zakpullen — Fri, 23 Jan 2026 14:44:05 GMT

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

Re: Create flag against employees that change cost centre by month

marcus_sommer — Fri, 23 Jan 2026 14:59:32 GMT

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.

Re: Create flag against employees that change cost centre by month

Daniel_Castella — Tue, 27 Jan 2026 12:07:05 GMT

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

Re: Create flag against employees that change cost centre by month

zakpullen — Tue, 27 Jan 2026 14:28:07 GMT

Hi Daniel,

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

Thank you so much.

Re: Create flag against employees that change cost centre by month

Daniel_Castella — Tue, 27 Jan 2026 15:53:45 GMT

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