Replacing consecutive values multiple times in one column and extending rows

[riyarojan]

Hello,

I am very new to Alteryx. I need help with my workflow where I can replace the original values that look like "16:00, 16:30, 17:00, 17:30...etc." to "5:00, 5:30, 6:00, 6:30...etc." where the starting point is 16:00 and so on and it needs to be replaced to 5:00 and so on. I will attach a screenshot of what the data looks like and what it should look like. I tried to use the Find and Replace Tool but for some reason the time is completely jumbled up. I also know the values in that column are a string so the tool should be able to work but it doesn't.

You can also see in the screenshot that in my output, I need to extend the rows where it starts at 0:00 but until it gets to 5:00 there are 0s in the columns to the right. Keep in mind that I need to do this process multiple times in the same column. For example, the correct output should have 48 rows that have the time numbers (0:00-23:30) and this occurs seven times in the column, skipping a few rows a time. I will also attach a screen shot of that as well. 

Please respond as soon as possible as I am with a deadline with this project.

[Qiu]

@riyarojan 
It will be much better you can provide your sample input and output in Excel format.
Maybe dateTime function and Generate Rows tool are what we should look into.

[Yoshiro_Fujimori]

Hi @riyarojan ,

I focused on generating the required rows. I hope this helps.

Due to the recent change of my work environment, I cannot attach an image. Please check the .yxmd file.

Input

You can specify the start date, number of days you want to repeat, number of rows you want to insert at the beginning of each day.

Date	Days	Skip
2023/7/2	7	3

Output

Date	Time
2023/7/2
2023/7/2
2023/7/2
2023/7/2	0:00:00
2023/7/2	0:30:00
2023/7/2	1:00:00
2023/7/2	1:30:00
2023/7/2	2:00:00
2023/7/2	2:30:00
2023/7/2	3:00:00

[riyarojan]

Hi @Yoshiro_Fujimori

Thank you so much this looks like it could work! I was wondering if there is a way to make this not hard coded on a specific date/week. My workflow will be inputing data from various weeks but still have the same time format of 0:00-23:30.

Thank you!

Riya 

[riyarojan]

Hi @Yoshiro_Fujimori,

Actually, you can ignore my recent reply I realized I can just deselect the date field and keep the time field. However, how can I replace my first field with the time field but also keeping those 3 rows that include letters like "IDP start" etc in the 3 rows that are null in the time field?

Thank you for bearing with me!

Riya

[Yoshiro_Fujimori]

Hi @riyarojan ,

I added Text Source to fill in to the first blank rows. Hope this helps.

Input

Text

1st row text

2nd row text

3rd row text

Output

Date	Time	Text
2023/7/2		1st row text
2023/7/2		2nd row text
2023/7/2		3rd row text
2023/7/2	0:00:00	0:00:00
2023/7/2	0:30:00	0:30:00
2023/7/2	1:00:00	1:00:00
2023/7/2	1:30:00	1:30:00
2023/7/2	2:00:00	2:00:00

[riyarojan]

Hi @Yoshiro_Fujimori,

This helps a lot! I will use the join tool to join this data with my original data. The only problem I have is that I'm not sure how to insert blank rows above the data on all the fields except data. I need to insert 16 blank rows before the start of the data, but the blanks should correspond with the time data till 8:00 and then the original data can start. The blank rows need to repeat 7 times in the data. 

I will include a screenshot of what the output data should look like. 

Thank you!