How to assign a string based on a condition from two column dates ?

[NaveenB]

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

[NaveenB]

you are awesome...Thanks! 

[NaveenB]

small doubt, I could see a grouping option in Multi-Row-Field (select the field) then why to mention in the expression?

[atcodedog05 · Accepted solution]

@NaveenB 

Yup you have found it 🙂

What you are thinking is a better approach.

Configuration like below which is much more simpler and suggestable approach.

Workflow:

There is multiple ways how you can reach the same solution.

Its just people's style and preference on how they approach it. 

Hope this helps 🙂

[NaveenB]

Great..thank you again 🙂

[atcodedog05]

Happy to help 🙂 @NaveenB 

Cheers and Happy Analyzing 😀