Subject: Luke – Task 1 & 2 status and open items
Hi Akshay,
Thanks for the additional census files. Task 2 is built — file attached. A few items need your input before I finalize.
[Attach: Luke – Transitioned Caregiver Master List_6-16-2026.xlsx]
Employee ID and EDL
Pulled Employee ID and the EDL components from the three EXTERNAL census tabs, matched on name. EDL is built as Entity (Finance GL Entity) – Department Code – Location. I included two versions: option A uses the Finance Cost Center Location field, option B uses the numeric extracted from the HR location code. Please confirm which you want as final.
I have left the lookups as live formulas rather than pasting as values, so the columns can be adjusted easily if any mapping needs to change.
Items to flag:
- 152 caregivers appear in the drops files but not in any of the three census tabs, so they have no Employee ID or EDL. I kept them in the list and marked them Keep-noID rather than dropping them. Let me know if you can provide census coverage for these.
- For 94 rows the Department Code differs between the census and the drops file. I built EDL using the census value. Please confirm the census is authoritative for EDL, or if the drops/transition-time department should be used instead.
- I added a dedup flag column (Keep / Drop / Keep-noID) rather than deleting duplicates outright, given the no-ID rows. Duplicates are flagged Drop keeping the earliest Transition Date. Let me know if you’d like the Drop rows removed.
No source files were modified.
Best,
Alan