Transfer In and Out Scenario

[Anonymous]

Hey Guys,

I got struck in a scenario to find out the Transfer In/Out of Employees.

I am having a transaction table with Fields like Global ID, Employee Code, Geo, Period etc

Now scenario logic is: If a employee with Global ID is not having same Geo in compare with Period, its mean he got the transfer. For Previous Month, employee will be OUT and for current month it will be IN.

I tried to build at script like after sorting the transaction records:

If(GlobalID=Previous(GlobalID) and Geo<>Previous(Geo) and Period<>Previous(Period),'IN','OUT') as Transfer

But It is giving me 'OUT' for every record if their is no change in Geo after a certain period of time, whereas it should be 'IN' if no change in the state in coming time period.

Please suggest.

Regards,

Balraj Ahlawat

[ogster1974]

How about doing a count on geo against a given employee?

If geo count = 1 then they haven't transfered

If geo count > 1 then they have

Regards

Andy

[niclaz79]

Hi,

I've made a similar application, and the solution I had was to duplicate the records for the ones that have moved. As example, a person that has moved from Location A to Location B has both an 'IN' and an 'OUT' in that period, depending on what Location you are looking at.

So you need to first flag which ones have changed location, and then create extra rows in the new period with the previous location (so you can find what location they left previous period when selecting a specific period). Then flag the Transfer as 'IN' or 'OUT' as mentioned. The ones that have not moved you don't need to flag.

Just make sure that for the extra lines you create, you don't count those in the Location which they left.

Hope this helps!

[Anonymous]

Agreed with Geo count!

But how to show 'IN' and 'OUT' for a period?

[Anonymous]

How to create extra lines?

Does this will make sense that we are adding extra lines in transaction table and increasing the size?

[niclaz79]

As mentioned above, you flag those that are changing compared to previous period (like you are doing) and then create the extra line from previous period with the 'OUT' flag.

As you need any given person to have both the 'IN' and 'OUT' flag when transferring, you need extra lines. I'm not sure how big the organization is you are working with, but the extra lines for the occassional transfer should not impact your application unless you have many million employees.