Replacing consecutive values multiple times in one column and extending rows

Question

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.

og data.png

correct output.png

repeating data in same column.png

riyarojan · Answer

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!

bla.png

Yoshiro_Fujimori · Answer

Hi @riyarojan ,

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

Input

Text1st row text2nd row text3rd row text

Output

DateTimeText2023/7/2 1st row text2023/7/2 2nd row text2023/7/2 3rd row text2023/7/20:00:000:00:002023/7/20:30:000:30:002023/7/21:00:001:00:002023/7/21:30:001:30:002023/7/22:00:002:00:00

GenerateDateTimeRows.yxmd

riyarojan · Answer

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