Create one variables to retrieve values from existing variables under certain conditions

[rubylin0113]

Hi, 

I am new to Alteryx. Right now I have one dataset with start date as an variable, I want to create one variable:

a) if it is later than January 2016, then assign value "January-2016"

b) if it is earler than January 2016, then let this variable equal to start_date.

How can i do this? 

thank you!

[RyanNewsome · Accepted solution]

Hi Ruby,

Firstly, if you are new to Alteryx the best way to start out is by first reading about all the common tools highlighted in the easy to follow documentation on the Atleryx website. I found that 95% of the questions you will ask yourself as a new user can be answered by using this documentation.

Here are some that will help you achieve what you are after, that relate to using the Formula tool

https://help.alteryx.com/9.5/Formula.htm

https://help.alteryx.com/9.5/Reference/Functions.htm

Here is an example workflow to show you:

The Text Input tool at the start I created the following 2 values (1 value before 1st Jan 2016, and one after)

It then uses the following expression in the Formula tool to create a variable called "desiredvalue"

IF [startdate] > '2016-01-01' THEN 'January 2016'
ELSE TOSTRING([startdate])
ENDIF

startdate must be converted to a string using the TOSTRING() function otherwise there will be a data type mismatch error due to startdate being a DATE and 'January 2016' being a STRING.

This type of question probably belongs more in the "Data Preperation and Blending"  discussion section.

Hope this helps

ryan

[rubylin0113]

Thank you so much!!!

Additional question related to this is now I have to variable "start_date" and "End_date" in string. And both of them in "yyyy-mm" format. How to calculate the time difference in month? I have attached a screenshot of these two variables.

I 've tried datetimediff function, but it doesn't recognize these two varialbes as valid date variable. 

Any idea about this? 

thank you!

[RodL]

Concatenate the "date" to what you already have to create a new field in a Date format.

The formula would be something like...

[Start_date] + "-01"

(You can use the Multi-Field Formula tool to do both of these fields at the same time.)

Then you should be able to use the two new fields with the DateTimeDiff.