Hi
Need your help to assign the expiry periods ( E1 or E2 or E3) based on date & expiry date ...In the below, if you notice each date has different expiry dates so I would like assign the nearest expiry date as E1, next closest as E2 and so on...while assigning the data the grouping is based on asset and the nearest expiry date is based on date.
hope my question is clear and I have provided the expected output as well.
I would really appreciate your support.
Asset | Value | Date | Expiry Date | Output |
Bank | 245 | 10/23/2020 | 10/29/2020 | E1 |
Bank | 246 | 10/23/2020 | 11/26/2020 | E2 |
Bank | 245 | 10/23/2020 | 12/31/2020 | E3 |
Non-Bank | 119.5 | 10/23/2020 | 10/29/2020 | E1 |
Non-Bank | 119.5 | 10/23/2020 | 11/26/2020 | E2 |
Bank | 246 | 10/22/2020 | 10/29/2020 | E1 |
Bank | 246 | 10/22/2020 | 11/26/2020 | E2 |
Bank | 246 | 10/22/2020 | 12/31/2020 | E3 |
Non-Bank | 119 | 10/22/2020 | 10/29/2020 | E1 |
Non-Bank | 119 | 10/22/2020 | 11/26/2020 | E2 |
Bank | 247 | 10/21/2020 | 10/29/2020 | E1 |
Bank | 247 | 10/21/2020 | 11/26/2020 | E2 |
Bank | 215 | 9/30/2020 | 10/29/2020 | E1 |
Bank | 215 | 9/30/2020 | 11/26/2020 | E2 |
Bank | 215 | 9/30/2020 | 12/31/2020 | E3 |
Non-Bank | 112.5 | 9/30/2020 | 10/29/2020 | E1 |
Non-Bank | 113 | 9/30/2020 | 11/26/2020 | E2 |
Non-Bank | 113 | 9/30/2020 | 12/31/2020 | E3 |
Bank | 215 | 9/29/2020 | 10/29/2020 | E1 |
Bank | 215 | 9/29/2020 | 11/26/2020 | E2 |
Bank | 215 | 9/29/2020 | 12/31/2020 | E3 |
Non-Bank | 112.5 | 9/29/2020 | 10/29/2020 | E1 |
Non-Bank | 112.5 | 9/29/2020 | 11/26/2020 | E2 |
Non-Bank | 112.5 | 9/29/2020 | 12/31/2020 | E3 |
Bank | 217 | 9/28/2020 | 10/29/2020 | E1 |
Bank | 218 | 9/28/2020 | 11/26/2020 | E2 |
Bank | 218 | 9/28/2020 | 12/31/2020 | E3 |
Non-Bank | 112.5 | 9/28/2020 | 10/29/2020 | E1 |
Non-Bank | 112.5 | 9/28/2020 | 11/26/2020 | E2 |
Non-Bank | 112.5 | 9/28/2020 | 12/31/2020 | E3 |
Bank | 210 | 9/25/2020 | 10/29/2020 | E1 |
Bank | 210 | 9/25/2020 | 11/26/2020 | E2 |
Bank | 210 | 9/25/2020 | 12/31/2020 | E3 |
Non-Bank | 110.5 | 9/25/2020 | 10/29/2020 | E1 |
Non-Bank | 110.5 | 9/25/2020 | 11/26/2020 | E2 |
Non-Bank | 110.5 | 9/25/2020 | 12/31/2020 | E3 |
Solved! Go to Solution.
Hi @NaveenB I mocked up a workflow that produces the output you describe let me know what you think?
@NaveenB
A bit different approach with @JosephSerpis and I try to make it dynamic.
Hi @NaveenB
Here my take on the task. I sorting just to be on the safer side with nearest date.
I am using a really handy tool called Tile tool for sub-index ID.
Output:
Workflow:
Hope this helps 🙂
If this post helps you please mark it as solution. And give a like if you dont mind 😀👍
you killed it with one tool..Thank you so much!
Thank you so much for your time and support...
The given solution really looks great but I have just shared the sample data not sure how it works with large data set with too many dates and expiry date combinations.
however given input, it looks perfect.
Feel free to reply back on this post if you run into any issues. Will always be happy to help 🙂
Thank you so much Joseph, I really appreciate your quick response.
your solutions looks perfect, both the options got right output.
I would like to understand this multi row formuale "IF [Row-1:Asset]=[Asset] and [Date]=[Row-1:Date] THEN [Row-1:Rank]+1 ELSE 1 ENDIF", it would be very helpful if you can throw some light?
Hi @NaveenB
I can help you out with that in his absence. Basically he is doing the same action as what tile tool is doing but with a Mutli-row formula.
IF [Row-1:Asset]=[Asset] and [Date]=[Row-1:Date] // grouping based on Asset and Date
THEN [Row-1:Rank]+1 // if from same group increment Rank (i,e 2 nd row of group get Rank 2)
ELSE 1 ENDIF // set Rank as 1 when new group starts.
Rank of his Multi-Row Formula = Tile Sequence number of Tile tool
Hope this helps 🙂