Comparing Data from Two Datasets

alexisjensen

I have a set of data with periods P01 - P12 (also represented by a date field with end of month date in it). In each period, each employee has a record and there is a field (Value) with a numerical value.  I need to create a calculated boolean field that for each employee determining:

IF (For employee) Previous period value = 0 and Current Period value >0 THEN "Y" ELSE "N"

I would appreciate any suggestions for the best practice / cleanest way to achieve this.

Regards,

Alexis

tcroberts

You could order by: Employee ID asc, then Period asc.

Then, pass to a MultiRow Formula tool, and group by EmployeeID.

You can now write a formula something like:

IF [Row-1:Value] = 0  AND [Value] > 0 AND [EmployeeID] = [Row-1:EmployeeID]

THEN "Y"

ELSE "N"

ENDIF

The check on employee ID is to be certain that you're only looking at one group at a time. Since your formula depends on the value 0, you should also set the "Values for Rows that don't Exist" configuration to "NULL" so that there is no confusion.

Let me know if this helps,

Cheers!