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. AssetValueDateExpiry DateOutputBank24510/23/202010/29/2020E1Bank24610/23/202011/26/2020E2Bank24510/23/202012/31/2020E3Non-Bank119.510/23/202010/29/2020E1Non-Bank119.510/23/202011/26/2020E2Bank24610/22/202010/29/2020E1Bank24610/22/202011/26/2020E2Bank24610/22/202012/31/2020E3Non-Bank11910/22/202010/29/2020E1Non-Bank11910/22/202011/26/2020E2Bank24710/21/202010/29/2020E1Bank24710/21/202011/26/2020E2Bank2159/30/202010/29/2020E1Bank2159/30/202011/26/2020E2Bank2159/30/202012/31/2020E3Non-Bank112.59/30/202010/29/2020E1Non-Bank1139/30/202011/26/2020E2Non-Bank1139/30/202012/31/2020E3Bank2159/29/202010/29/2020E1Bank2159/29/202011/26/2020E2Bank2159/29/202012/31/2020E3Non-Bank112.59/29/202010/29/2020E1Non-Bank112.59/29/202011/26/2020E2Non-Bank112.59/29/202012/31/2020E3Bank2179/28/202010/29/2020E1Bank2189/28/202011/26/2020E2Bank2189/28/202012/31/2020E3Non-Bank112.59/28/202010/29/2020E1Non-Bank112.59/28/202011/26/2020E2Non-Bank112.59/28/202012/31/2020E3Bank2109/25/202010/29/2020E1Bank2109/25/202011/26/2020E2Bank2109/25/202012/31/2020E3Non-Bank110.59/25/202010/29/2020E1Non-Bank110.59/25/202011/26/2020E2Non-Bank110.59/25/202012/31/2020E3

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

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

Dynamic Processing

Accepted answers

JosephSerpis

Hi @NaveenB I mocked up a workflow that produces the output you describe let me know what you think?

Expiry_Date_24102020.yxmd

atcodedog05

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 😀👍

E.yxmd

atcodedog05

@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 🙂

E.yxmd

All comments

JosephSerpis

Hi @NaveenB I mocked up a workflow that produces the output you describe let me know what you think?

Expiry_Date_24102020.yxmd

Qiu

@NaveenB
A bit different approach with @JosephSerpis and I try to make it dynamic.

1025-NaveenB.yxmd

atcodedog05

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 😀👍

E.yxmd

NaveenB

you killed it with one tool..Thank you so much!

NaveenB

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.

NaveenB

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?

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335