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

[Inactive User]

Sort on employee ID ascending, date ascending. Then use multi-row formula to look at the previous period/record and the current record simultaneously to configure the logic you require.

[tcroberts · Accepted solution]

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!