Deleting Rows

[Carolyne]

How would can I get this code to work right: In the first line, the end date is greater than the second start date I'd like to delete the 2nd, 3rd, 4th line and leave the 5th line since the start date in the 5th line is greater than the end date in the first line. Thank you.

Customer ID	Start Date	End _Date	Invoice ID
1	2/28/2019	3/6/2019	125
1	3/1/2019	3/3/2019	489
1	3/2/2019	3/3/2019	123
1	3/4/2019	3/5/2019	456
1	5/5/2019	6/1/2019	236

End result

Customer ID	Start_Date	End_Date	Invoice ID
1	2/28/2019	3/6/2019	125
1	5/5/2019	6/1/2019	236

[CharlieS · Accepted solution]

Hi @Carolyne 

The "deleting rows" here will be achieved by filtering our data to only the rows we want to keep. Defining the rows we want to keep can be achieved with a few Multi Row Formula tools.

- In Alteryx, the DateTIme functions only work with fields formatting in the ISO 8601 date format (YYYY-MM-DD). I start this process by using a Multi Field Formula tool to convert the two string fields with date information into this format and change the field type. I also added a Sort here to make sure the start date values were sequential.

- Now that we have the date fields, we can begin applying some sequential logic. I start by establishing a [LocalMax] value of End Dates. This is the sequence of the greatest End Date value.

- With the running [LocalMax], I can use another Multi Row Formula tool to determine if the start date for each record is greater than the previous [LocalMax] value, so we know that we want to keep that record.

- Finally a Filter will return on the field we want to keep and a Select tool will remove the additional fields I created.

- As a final note, I also added a "GroupBy" on the [Customer] field so this sequential analysis will be evaluate each Customer separately. 

Check out the attached workflow and let me know if you have any questions. 

[Carolyne]

Thank you so much.