Date Transformation

[swapsingh2712]

Hello All,

I've a data set where for a particular test, there's an activation date  and decommision date columns. I want to transform that data set into a new file where it shows all the dates that are mentioned between  activation date  and decommision date columns in one column. The format should be in mm/dd/yyyy format.

For example if the activation date is 1/1/2018 and decommission date is 8/30/2018. the new transformed column should show dates like this

1/1/2018
2/1/2018
3/1/2018
4/1/2018
5/1/2018
6/1/2018
7/1/2018
8/1/2018

I've attached the screenshot for better understanding

Could you please help

BR,

swapsingh

[mbarone]

If you have two incoming dates and want all dates between them, first you'd have to transform the mm/dd/yyyy format to ISO format, which Alteryx will recognize as a date format.  You would use the DateTimeParse function (DateTimeFormat in the help files) for that.

Then, you can use the Generate Rows Tool to populate the dates in between your two dates.  I would get your two dates next to each other in separate columns, but the same row, and in the Generate Rows Tool , create a new field, with your initialization being the first date, and your looping function to be  DateTimeAdd([new_field],1,'month'), and the condition to be [new_field]<=[other date field].

Play around with it and let us know if you get it or where you need help.

[afv2688]

Hello @swapsingh2712 ,

Just adding a workflow to what @mbarone  mentioned 🙂

Regards

[swapsingh2712]

Hello @afv2688 

Thank you for your response. I tried using the same work flow but getting error.

Please find the attached screenshot in the attachment.

Also I've attached the preview of my data set. Actually for few of the records, decommission date is not available.

Could that be the reason for the error?

KR

[afv2688 · Accepted solution]

Hello @swapsingh2712 ,

I have added another examples. The first on the top will be for dates that have not been converted into dates and appear as strings.

The example on the bottom is for data that is input as dates directly so no transformation is needed.

Regards

[swapsingh2712]

Hey @afv2688 

I'm getting the attached error.Is it because decommission date for most of the records are empty in my dataset?

If yes, Can I make changes to the existing workflow where I can only generate dates if decommission date is available?

Awaiting your response.

KR

[afv2688]

Hello @swapsingh2712 ,

That should not be the case, the error is shown whenever you want to generate something that would loop forever for example. Can you show me the data before the generate rows and also how you have the tool configured? Something like this:

I do not need to see the whole data, only the columns that we are concerned about.

Regards

[swapsingh2712]

hello @afv2688 

I've attached the screenshots.

Let me know if it helps

KR

[afv2688]

Ok understood the error. Your values are not actual nulls, they are strings with [Null] written on it.

Try this one.

Regards

[swapsingh2712]

@afv2688 

I'm still getting the same error 😞