Consolidating dates

Question

Hi, I have 4 fields: Progression date, progression end date, promotion date, and promotion end date. I want to use a formula to consolidate these dates to get [Effective date] and [Effective end date], so that it accounts for both progression and promotion dates.

Essentially the logic is for the first effective date/end date, take the earliest of either progression or promotion date (and end date). and for the following effective dates after that, it should take the earliest of either progression/promotion date, but AFTER the previous row's effective end date.

Any help would be appreciated. Below is the expected output:

Progression dateProgression end datePromotion datePromotion end dateEffective dateEffective end date1/01/201825/02/20181/01/20181/07/20181/01/201825/02/201826/02/201824/02/20191/01/20181/07/201826/02/20181/07/201826/02/201824/02/20192/07/20182/12/20182/07/20182/12/201826/02/201824/02/20193/12/201830/06/20193/12/201824/02/201925/02/201923/02/20203/12/201830/06/201925/02/201930/06/201925/02/201923/02/20201/07/20191/12/20191/07/20191/12/201925/02/201923/02/20202/12/201912/07/20202/12/201923/02/202024/02/20207/01/20242/12/201912/07/202024/02/202012/07/202024/02/20207/01/202413/07/202013/12/202013/07/202013/12/202024/02/20207/01/202414/12/202011/07/202114/12/202011/07/202124/02/20207/01/202412/07/202110/07/202212/07/202110/07/202224/02/20207/01/202411/07/20229/07/202311/07/20229/07/202324/02/20207/01/202410/07/20237/01/202410/07/20237/01/2024

Bren_Spill2 · Accepted Answer

Hi @hcao - you can achieve this with a multi-row formula tool.

To test, create a new field called Effective Date2, making sure that it's date type, and use the following formula:

if isnull([Row-1:Effective Date2]) then min([Progression date],[Promotion date]) elseif

min([Progression date],[Promotion date]) <= [Row-1:Effective Date2] then max([Row-1:Effective Date2],[Progression date],[Promotion date]) else Null() endif

Repeat for end date

You will also need to convert your dates to Alteryx date types for this to work. You can do this for each column using a datetimeparse formula: DateTimeParse([Progression date],"%d/%m/%Y")

TUSHAR050392 · Accepted Answer

Hey @hcao Another approach -

1. First convert all your 4 columns to date using date time tool.

2. Then use the Record ID field to create an id.

3. Then use a formula tool to create 2 new columns - Effective date - MIN(Progression date,Promotion date) and Effective end date - MIN(Progression end date,Promotion end date)

4. Use Multi row formula tool and update Effective date column and use formula - IF Record Id = 1 then Effective date else MAX(Progression date,Promotion date, row-1: Effective end date) ENDIF

Hope this helps