Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Date Transformation

Highlighted
7 - Meteor

How can I change a month level date into a day level date?

 

For example right now my dates are 1/1/20, 2/1/20

 

But instead I want every day of the months listed 1/1/20, 1/2/20, 1/3/20, etc for the whole year

Highlighted
Alteryx
Alteryx

Hey @barbaracnx 

 

I would recommend taking a look at the Generate Rows tool. Utilizing the DATETIMEADD() function

 

DATETIMEADD([day_column], 1, 'days')

 

Here is some help documentation on the Generate Rows Tool. Let me know if you have any questions

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

hi @barbaracnx,

 

if it's to generate the dates, refer to @NicholasM post, if it's about the formating, you have to use 2 functions, datetimeparse and then datetimeformat to get the format you want (find a sample workflow attached).

Highlighted
7 - Meteor

barbaracnx_0-1577718273332.png

 

That for some reason isn't returning anything... am I doing something wrong.

 

To clarify I want to change 1/1/20 into 1/1/20, 1/2/20, 1/3/20, 1/4/20, etc

Highlighted
Alteryx
Alteryx

Hi @barbaracnx,

 

now I understand what you want to achieve, in this case you want to create records.

 

for that you want to make sure you have a date data type (use the datetime format tool in case your date column is not yet with that data type)  

 

Use the formula tool to calculate the end of the month for each row

 

then use the generate rows tool where you iníciate the value with the first record of the date column and you increase the value by one day until the date is smaller or equals to las day of month column 

 

have a look into this example. This shows you the configuration of the generate rows tool 

 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Generate-Date-values/td-p/233

 

Let me know if you have questions


best,

vianney

 

 

Best,
Vianney
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

here is a sample workflow with the combination of both generate row and the date formating, you first transform the dates into dateformats with a formula, then you use another formula (datetimetrim) to get the first and last of the month and finaly you can use a generate rows to have all the rows for all the possible days in the month.

 

Hope it helped!

Highlighted
7 - Meteor

Perfect! Thank you!

Highlighted
5 - Atom

Hello, @barbaracnx.

 

To change the date format you can also use the tool Convert Date. It's pretty much easy.

 

First you just have to input the tool and choose the format to convert. If i's String to Date or Date to String.

 

1.JPG

 

Than you select the field you want to convert and specify the name of the new column that will be created with the new data.

Once you've done that, it's time for you to choose the format you want to use:

 

2.JPG

 

The tool will create a new column with the information you created.

 

Hope it helps!!

 

 

 
 
Labels