Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Combining dates

Riot
8 - Asteroid

So I am have broken down a numaric date, but I would like to convert the numerical month and year into a variable that I can enter into a decision tree or forrest model. I have been messing with formula for awhile now but I cant seem to get it. Would would be the best way to do it and what would a good field variable be?  Thank you. 

10 REPLIES 10
s_pichaipillai
12 - Quasar

Hi Riot,

sorry i am little confused

do you want to combine the Month , Year numeric fileds then convert to dateformat and then pass the dateformat to another?

 

Riot
8 - Asteroid

Yes, so I have numeric Months and Numeric years. I want to combine them so I can test and see if the month and year are a factor in the occurance of certain events. So I was wondering how to combine them, and then what field type (byte, INT16, string, etc.) I should switch them too. 

s_pichaipillai
12 - Quasar

Riot,

 

PFA workflow with some sample data.

there are more ways to do that , but to keep it simple i used formula tool and dataformat , parse function

like below

DateTimeParse((tostring([Month])+"/"+"1"+"/"+tostring([Year])),"%m-%d-%Y")

1. change your month and year type to string (you can do it using select tool or tostring function)

2.then use datetime parse function with required format

please refer the link ( http://downloads.alteryx.com/Alteryx8.6.2/WebHelp/Reference/DateTimeFunctions.htm )

 

then output will be something like below

riot.PNG

Riot
8 - Asteroid

I really appreciate the help, the problem is that I can not submitt the "DateTime" data field into a Niave Bayes or Decision tree as a variable selection. Does that make sense ? 

RodL
Alteryx Alumni (Retired)

Not sure I understand how the dates would fit into the classification model, but...

 

If you want to look at the date as for of a continuous variable (along a time continuum), then I would configure it as an integer in the format YYYYMMDD. The formula for that would be...

(YYYY * 10000) + (MM * 100) + (DD)

...so that October 29, 2015 would be 20151029. This keeps the dates in an 'ordinal' type of format (with the numbers getting bigger as dates progress) where there is meaning within the classification where something would be true/false based on when in the entire time frame it took place (e.g., something will be more likely to happen after a certain date).

 

If instead you are more interested in whether something is more likely true/false in a given month of the year (to say provide some sort of seasonality to your tree), then I would not include the year info at all, but only use the month numbers. In that case I would format 01, 02,...11, 12 as strings and treat that variable as levels in the category (e.g., where something will be more likely to happen in certain months of the year).

 

Rod

 

Riot
8 - Asteroid

Interesting gentlemen. I am trying to figure out if the date affects if certin things are more liekly to occur or not. I would be interested in trying to use the Day Month year, but I would have trouble parsing it. One set of data has the dates as YYYY-MM-DD and the other has it as M/DD/YYYY, I know how to remove the - and the / from the dates, but I do not know how to move them to a DDMMYYYY format. 

Riot
8 - Asteroid

Here is dummy data of the data I am working with

s_pichaipillai
12 - Quasar

Riot,

you check the attached sample workflow to convert dateformat to DDMMYYYY

well, there are couple of ways but i just used one of them for you

Riot
8 - Asteroid

Thanks, I think I can work with this and figure it out. Thank you very much. 

Labels