Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

NaveenB
6 - Meteoroid

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 DateOutput
Bank24510/23/202010/29/2020E1
Bank24610/23/202011/26/2020E2
Bank24510/23/202012/31/2020E3
Non-Bank119.510/23/202010/29/2020E1
Non-Bank119.510/23/202011/26/2020E2
Bank24610/22/202010/29/2020E1
Bank24610/22/202011/26/2020E2
Bank24610/22/202012/31/2020E3
Non-Bank11910/22/202010/29/2020E1
Non-Bank11910/22/202011/26/2020E2
Bank24710/21/202010/29/2020E1
Bank24710/21/202011/26/2020E2
Bank2159/30/202010/29/2020E1
Bank2159/30/202011/26/2020E2
Bank2159/30/202012/31/2020E3
Non-Bank112.59/30/202010/29/2020E1
Non-Bank1139/30/202011/26/2020E2
Non-Bank1139/30/202012/31/2020E3
Bank2159/29/202010/29/2020E1
Bank2159/29/202011/26/2020E2
Bank2159/29/202012/31/2020E3
Non-Bank112.59/29/202010/29/2020E1
Non-Bank112.59/29/202011/26/2020E2
Non-Bank112.59/29/202012/31/2020E3
Bank2179/28/202010/29/2020E1
Bank2189/28/202011/26/2020E2
Bank2189/28/202012/31/2020E3
Non-Bank112.59/28/202010/29/2020E1
Non-Bank112.59/28/202011/26/2020E2
Non-Bank112.59/28/202012/31/2020E3
Bank2109/25/202010/29/2020E1
Bank2109/25/202011/26/2020E2
Bank2109/25/202012/31/2020E3
Non-Bank110.59/25/202010/29/2020E1
Non-Bank110.59/25/202011/26/2020E2
Non-Bank110.59/25/202012/31/2020E3
14 REPLIES 14
JosephSerpis
17 - Castor
17 - Castor

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

Qiu
20 - Arcturus
20 - Arcturus

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

atcodedog05
22 - Nova
22 - Nova

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:

atcodedog05_0-1603610194881.png

Workflow:

atcodedog05_1-1603610209518.png

Hope this helps 🙂


If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

 

NaveenB
6 - Meteoroid

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

atcodedog05
22 - Nova
22 - Nova

Glad you found this tool exiting 🙂

 

Happy to help 🙂 @NaveenB 

 

Cheers and Happy Analyzing 😀

NaveenB
6 - Meteoroid

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.

atcodedog05
22 - Nova
22 - Nova

@NaveenB 

 

Feel free to reply back on this post if you run into any issues. Will always be happy to help 🙂

NaveenB
6 - Meteoroid

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? 

atcodedog05
22 - Nova
22 - Nova

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 🙂

 

Labels