Capstone Project - Audit user access

[GGAlt3ryx]

Hi all, I am having trouble completing the capstone project - the last Getting Started Learning Path and am also having even more trouble finding the solution to the exercise. There seems to be multiple projects entitled "Capstone".

The project im referring to involves audit user access of current users to those employees terminated and new hires and flagging discrepancies 

[Qiu]

@GGAlt3ryx 

Maybe you need to check here.

https://community.alteryx.com/t5/Practice-Exercise/Learning-Path-1-Capstone-General/m-p/474140#M17

[GGAlt3ryx]

This is for a different capstone project.

[kajoljoshi]

Hi,

I am looking for the same output, are you able to find out 

[ArnaldoSandoval]

Hi KajolJoshi,

This is how I resolved the problem posted by GGAlt3ryx using his attached workflow.

Input Data:

The Current Users data is challenging, fortunatelly @ACE Qiu link proved to be very helpful. The first thing we notice when looking at this data is its presentation, each field (column) contains an user record, and the column names are also there; the screenshot below highlights that the actual data for each user are on the odd rows. This is important.

The Terminations are given to us in a file with a record per row with all fields separated by pipes, a Text to Column tool deal with this structure easily.

The New Hires data is given in a table and we do not need to do anything, it is ready to be use.

Processing Current Users:

1. We add a RecordID to the source data, because we only need even records.

2. We select the even records, e.g. 2, 4, 6, ...

3. We transpose the data on the RecordID field, this step place all the users data in one single column, the Value column.

4. Now we Cross Tab the data on the Name field, concatenating the Value column in the process, this step actually shuffle the data wich is now one record per user, without column headers 🙂

5. Now we use the Select tool to assign names to the columns; for this scenario is the best approach, if we were working with dynamic data perhaps we need something different, which is not the case with this example.

At this point our Active User data is ready to be consumed (joined) with the Terminations (once it is ready).

Processing Terminations

1. This is very simple, just split the field by pipes, parse the date and using a select tool assign names to the column.

2. We can now join this data with the active users to identify the people who are gone, and format the output as requested; this section also identify just the Active User, we need them to be merged with the new hires.

Processing New Hires

1. We need to join the New Hire with the Active Users; this join Left, Right and Join have meaning for this process as documented in the solution.

I am attaching the workflow to this reply.

Hope this helps,

Arnaldo