Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Advanced row comparison

svm
6 - Meteoroid

Hi,

 

I have a data set with HR data. It consists of the following columns:

- Employee Number

- Employee Name

- Employee Salary

- Work Place (City)

- Work Place (Country)

- Department

- Line Manager

- Report Date

 

I get this report every month end.

 

What I would like to do is add a column to log changes, so I would like to compare two dates (end June with end May) and per Employee and write into the change column e.g. new City if the city has changed, new country if the country has changed etc. In addition:

- If the employee cannot be found the month before, then write "new" in the column.

- If the Employee was there the month before but not anymore, then copy the row of the month before and write "leaver" into the change column but if in the month before it says "leaver" in the change column then do not write a new row (as the employee can leave only once).

- If several thins change (e.g. salary and department, then write both changes into the change column (e.g. "change salary, change department")

 

Thanks for any good ideas.

 

Sven

3 REPLIES 3
ivoller
12 - Quasar

Sven,

 

 

Look at Multi-Row Formula tool (grouped by employee).

 

The New Field would be called Changes.

 

The expression would include nested statements like :-

 

if [field] != [row-1:field] then [Changes] + ", change " + [field]

 

Alternatively you could use a Summarize tool with Concatenated values for each employee

 

Iain

 

 

Iain

david_fetters
11 - Bolide

So I think the first step will be to use the transpose tool with Employee Number as your key (which I'll assume is consistent) for the current month and the previous month.  That will give you a row for each of your other columns, all linked together by the Employee Number column.  Then Join both the current month and previous month data on Employee Number and Name (which is a field name).  That will give you a column for Employee Number, Name (of transposed field), the previous months value, and the current months value.  In a formula tool, you can create a new column called "Changes" with a formula like: 

IF [Old_Value] != [Current_Value] THEN "New "+ [Name] ELSE "" ENDIF

 Then you can use the Summarize tool to group by Employee Number and concatenate your [Changes] column, separated by commas. 

 

To detect new hires and 'leavers', take the output from the first Join tool's left and right exits, unique on Employee Number, and add a formula tool with a [Changes] column set to "new hire" or "leaver" as the case may be.  Union all of these summarized results (your join outputs plus the comparison results from old/new values) and join it back to your report on Employee number.

 

EDIT: Before transposing, it might be easier to do your newhire and leaver detection rather than doing it on the transposed data. 

 

Let me know if you have trouble implementing this, but its a good approach to comparing two identically schemaed datasets.

 

 

svm
6 - Meteoroid

Thanks - of course, find the new / leavers via the right and left join, how could I oversee that in my thinking. Appreciate the quick response. 

Labels