Help - Adding rows for reservations greater than a day

[cguerra]

Hello - I'm trying to figure out what workflow to use when adding multiple rows for a reservation greater than a day. For example, this is how the record comes in (first table):

Note: I add in the "Check" column to show where the reservations start and end date are not equal. In the second table, I manually add in a row for each day. I greatly appreciate the help. 

Conclusion: For example, the raw data (table 1) shows one record for id 1. If I wanted to capture the duration of this reservation it would be 1 hour. Versus the cleaned data (Table 2), same record, the duration is 3 hours because I separated the reservation into 3 rows. 

id	StartDate	EndDate	Check	StartTime	EndTime
1	1/1/2018	1/3/2018	False	8:00 AM	9:00 AM
2	1/2/2018	1/3/2018	False	9:00 AM	11:00 AM

Final output. Cleaned after I manually add in the rows in excel. 

id	StartDate	EndDate	Check	StartTime	EndTime
1	1/1/2018	1/1/2018	True	8:00 AM	9:00 AM
1	1/2/2018	1/2/2018	True	8:00 AM	9:00 AM
1	1/3/2018	1/3/2018	True	8:00 AM	9:00 AM
2	1/2/2018	1/2/2018	True	9:00 AM	11:00 AM
2	1/3/2018	1/3/2018	True	9:00 AM	11:00 AM

[Claje · Accepted solution]

Hi,

I'm going to ignore your "Check" column for now because I think you understand the business logic there better than I do.

The Generate Rows tool will be a big help here.

I've attached an example that is also using the DateTime tool to convert your dates into the Alteryx date format.

Hope this helps!

EDIT: My original upload had the wrong logic for End Date.  I have uploaded a new version that corrected the End Date logic and this should fully meet the need.

[cguerra]

Claje,

This is exactly what I was looking for. Thank you.

[cguerra]

Hello Claje,

I was trying to implement what you did to my workflow. I'm using an excel file as my input data. The only difference is where you have string to date/time format selected, I have date/time format to string selected for both start and end date. In the select tool I have the new start and end date as "Strings" versus you have these as "Dates". When my workflow completes running I get an error saying "DATETIMEADD: "12/08/2017" is not a valid Date Time" and so forth. The workflow completes but does not add in the additional rows of data. Can you please help me with this issue. 

Thanks

[Claje]

Hi,

In the example workflow I attached previously, I used two DateTime tools to convert from strings to Dates in Alteryx so that the formulas would work.  If you try copying that layout it should work for you!

If you have trouble getting that piece to work let me know and I can try to help.  Basically, you really want these to come across in Alteryx's native Date format so that you can use functions like DateTimeAdd effectively

[cguerra]

Hello - Yes, I did that. I think where I'm having the issue is that the input data "Start Date" and "End Date" are saved as a date format in excel and not a string. Do you think this is the issue?

[Claje]

If you attach a Select tool to your input data and these are listed as "Date" formats, then you should be all set and can skip the DateTime tools.  That could definitely be causing an issue!