Date Range Count Number of Days

Question

Hello

Need help for this situation,

Given :

NameStartDateEnd_DateNo. of daysCable1/31/20231/31/20231Sunspot2/15/20232/15/20231Domino3/20/20233/21/20232Cannonball3/31/20233/31/20230.5Feral4/3/20234/4/20232Rictor4/14/20234/14/20231Shaterstar4/19/20234/19/20231Boom4/26/20234/26/20231John5/3/20235/5/20233

I want the output to be break down into 1 day per row, output should be like this:

Output:

NameStart_DateEnd_DateNo. of daysCable1/31/20231/31/20231Sunspot2/15/20232/15/20231Domino3/20/20233/20/20231Domino3/21/20233/21/20231Cannonball3/31/20233/31/20231Feral4/3/20234/3/20231Feral4/4/20234/4/20231Rictor4/14/20234/14/20231Shaterstar4/19/20234/19/20231Boom4/26/20234/26/20231John5/3/20235/3/20231John5/4/20235/4/20231John5/5/20235/5/20231

Thanks in advance for the help, Thank You :)

ChrisWaspe · Answer

Hey @Grail030510 . I would use a generate rows tool to get you what you want. With a little data prep you can get the result you want. The Generate Rows tool takes some getting used to but you essentially need to create some logic to loop until a condition is met. Please the attached workflow.

Date Range Count Number of Days_CW.yxmd

JosephSerpis · Answer

Hi @Grail030510 I came up with an approach using your start and end dates which needed to be converted into YYYY-MM-DD format to generate each day sequentially in the Generate row tools and then I formarted the date back into the orginal format of MM/DD/YYYY and deselected the uncessary fields that I made during the process.

Generate_Rows_28072023.yxmd

DataNath · Answer

Hey @Grail030510, here's a workflow which ought to provide what you're looking for. The steps breakdown as follows:

1) Generate extra rows. Using the generate rows tool, we tell Alteryx to make N rows, where N = your No. of days field. Note: We have had to used the Ceil() function here, as for rows like Cannonball, where you have 0.5 days, generate rows wouldn't generate any days here and so ceil force rounds this up to 1 in order to give it at least a single row.

2) Use a formula tool to simply set the [No. of days] value to 1 now.

3) Select drops the temporary RowCount field used when generating additional rows.

Hope this helps - please do ask if you have any questions!

Generate row for each day in range.yxmd