Narrowing down data

[Lizbhernan]

Hello!

I am currently working on a workflow and I am having trouble narrowing down my duplicates. For this work flow, we have an employee that has multiples leaves of absence but we would only pay on one of those LOAs with the highest priority. I added a priority mapping and now I am trying to use the formula tool to narrow down to only populate one leave of absence. 

I need my results to look like the Intended Results I attached (the formula is working as it should just not for what I need it)

Can someone please help? 

[MilindG]

@Lizbhernan 

[Qiu]

@Lizbhernan 
If I am not misunderstanding your intention here, we can simply use a Sample tool to take the first record for each Employ Number after sorting by the Priority existing.

[Lizbhernan]

@MilindG @Qiu Essentially the employee number is not necessarily important. I just need to be able to filter out the leave of absence. I added a mapping table (priority #s) to identify which leave of absence has what priority. For example priority 1(FMLA) would trump over any other leave of absence so if an employee has 3 different LOAs (Priority 1, priority 2 ,and priority 3) 2 AND 3 would be dropped and only 1 would stay. However, if they have a leave of absence priority 2 and 3 then 3 would have to come off the workflow and only leave 2. 

[Qiu]

@Lizbhernan 
I am bit confused.
So in general, if an employee takes 3 types of leaves, then only the hight priority stays? right?
and you priority mapping is giving the same priority for two types of medical leaves, what is the logic there?

[Lizbhernan]

Yes, that is correct.

Yes, there are multiple leaves that have the same priority (I included the mapping for reference). I'm trying to figure out a logic in which 1. FMLA always trumps any other leave type and 2. ensure that only one leave populates (even if they're both a priority 2). 

Maybe the approach I am taking might not work? I am open to suggestions 

[Qiu]

@Lizbhernan 
I am sorry but I still not quite understand the logic here.
maybe you are looking for an Unique tool?

[Lizbhernan]

@Qiu I apologize, I uploaded the incorrect mapping table that included priority 3. After looking at it, I ended up adding a 4th priority that can hopefully help with narrowing down my results.

I think if I use the unique tool, it might choose the wrong priority number. But let me try it

[Qiu]

@Lizbhernan 
Thank you for checking. Let me know if you need help, and I can try. 😁

[Lizbhernan]

@Qiu I tried using the sort tool and then the unique tool but it still gave employees more than one leave. If you can give it a try, I would appreciate it 😊