How to add a row while comparing two date fields ?

utkarsh26

I have the below data with me:

Name	Request Status	Start Date	End Date	Start Date2
ABC	4	11/21/2023	11/22/2023	11/15/2023
ABC	3	11/23/2023	12/1/2023	11/15/2023
ABC	6	12/4/2023	12/5/2023	11/15/2023
ABC	7	12/6/2023	12/7/2023	11/15/2023
ABC	6	12/8/2023	12/12/2023	11/15/2023
ABC	7	12/13/2023	12/13/2023	11/15/2023
ABC	4	12/14/2023	12/15/2023	11/15/2023
ABC	14	12/18/2023	12/18/2023	11/15/2023

Now, I need to compare the Start date column with Start Date2 column as:

if Start Date2 < min(Star Date), add 1st row as below:

Name	Request Status	Start Date	End Date
ABC	"Not Available"	11/15/2023	11/20/2023
ABC	"4"	11/21/2023	11/22/2023
ABC	"3"	11/23/2023	12/1/2023
ABC	"6"	12/4/2023	12/5/2023
ABC	"7"	12/6/2023	12/7/2023
ABC	"6"	12/8/2023	12/12/2023
ABC	"7"	12/13/2023	12/13/2023
ABC	"4"	12/14/2023	12/15/2023
ABC	"14"	12/18/2023	12/18/2023

Hammad_Rashid

To achieve this in Alteryx, you can use the following steps:

1. Sort Tool:

   • Sort your data based on the "Start Date" column in ascending order.

2. Formula Tool:

   • Create a new column named "Start Date2_Flag" using the formula:
     [Start Date2] < Min([Start Date])
     This will give you a True/False flag indicating whether "Start Date2" is less than the minimum "Start Date."

3. Filter Tool:

   • Filter your data to keep only the rows where "Start Date2_Flag" is True.

4. Union Tool:

   • Use a Union tool to combine the filtered data with the original data. Ensure that the columns match.

5. Formula Tool:

   • Create a new column named "Request Status" using the formula:
     IIF([Start Date2_Flag], "Not Available", [Request Status])
     This will replace the "Request Status" with "Not Available" for the rows where "Start Date2_Flag" is True.

6. Select Tool:

   • Choose the columns you want to keep in your final output.

The workflow above will add the required rows with "Not Available" in the "Request Status" column based on the condition you specified. Ensure that the column names and data types match between the original and filtered data to successfully perform the union.