Help Identifying New Jobs

[mallieteal21]

Hello - I am trying to identify the first time a new job shows up in the attached data set. For this request, I consider a "new job" to be any time a job code appears in the list for the first time with the Department name listed in the "OrgLevel3Desc" column. Would appreciate any help on the best way to identify these across the multiple rows. 

[Carolyn]

What I would do is:

1. Optional - Add a RecordID Tool so I could eventually re-sort the data to its original order (not sure if you'd want/need this, but I tend to do this often)
2. Sort by OrgLevel3 and then Record ID - this groups all the OrgLevel3 together and then RecordID puts it back in the original order, after it's grouped by RecordID. This might make more sense to do by Effective Date
3. Use a Multi-Row Tool to flag when it's listed for the first time. Group by OrgLevel3 and then it's just a basic IF formula
4. Optional - Re-sort to its original order

[Carolyn]

Same as described above except I changed the Sort to use Effective Date instead of RecordID since I probably should've done that to begin with :)

My assumption was that the data was already in the order you wanted to keep it, but that's not a good assumption to make

[SPetrie]

Another option is to use a unique tool, or a sample tool set to the first 1 row.

It will gab the first item, so in both options you need to have your data sorted into the proper order. Use by date or whatever method you think is appropriate for your results. Select your grouping columns for your first criteria and you are set. If you want the first time the job code and OrgLevel combo appears, select those two columns.

[Carolyn]

@SPetrie - Oh, those are spiffy ways to solve it. I feel like this is the second or third time that I've answered something and then you've had a much prettier answer. I'll get you next time ;)