Need Help Building a Quick Workflow to Transform Data

[katherinetdavita]

I'm under a significant time crunch and don't have time to figure this out myself - hoping someone can help me with a super quick workflow that can use the attached data on input tab to what it looks like on the output tab of the attached file.

[DavidSkaife · Accepted solution]

Hi @katherinetdavita 

Here you go, using the Generate Rows tool to build out between the start and end dates, then the Multi-Row Formula tool to increment the TMID (if that was what you wanted). Finally a Select tool to remove unwanted columns, and re-arrange:

[abacon]

This does the trick. 

[abacon]

@katherinetdavita , I missed the iterate the TMID column, @DavidSkaife's workflow does what you want.

[katherinetdavita]

Hi! Thank you for this! I just put some real data in and am getting an error!

[katherinetdavita]

@DavidSkaife , also realizing I made an error on that output data. Should all be the same TMID all the way down as in the attached new sample data

[Qiu]

@katherinetdavita 
Just remove the Multi-Row formula tool from the flow @DavidSkaife 😁

[katherinetdavita]

@Qiu Thank you - I had gotten that far trying to work through it on my own so it's good to hear I was on track. Now I seem to be having trouble with date formatting - always a struggle for me! Does that generate rows tool require the dates going in to be strings? And if so, what's the best fix for making them the right format when the input data has dates that look different as in the attached image? The workflow you provided works for the first few rows, but not the one with the 3/20 start date. Thanks again for your help!

[Qiu]

@katherinetdavita 

We can seperate the data stream by the the Date format and use the DateTime tool to format the String type to Date Type as below.