Alteryx Designer Discussions

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

'Tis the season to be spooky! Read our new blog, How Spooky is Your City? Mapping and Predicting Scary Stuff. In it, @SusanCS provides a fun glimpse into using data to figure out the creepy quotient of where you live! And don't forget to check out our Digital Costume Thread to get yourself in the mood for a candy binge!

SOLVED

Modifying a value user inputted from Text Box Tool - Analytic App

Highlighted
6 - Meteoroid

Hello!

 

I am trying to create an analytic app where user inputs 'MMMYY' (in text) and it will read in the particular SQL table based on the input. (I have an action tool to update the value of the specific string)

 

However, without them having to enter it again, I would like it so that it can read in a 2nd table, but the YY automatically adjust by 1 year prior.

 

I was also trying to parse the data and subtract 1 year and put it back in...

 

I am stumped and the only solution I can think of is asking the user to input 2 different dates (MMMYY)

 

Capture.JPG

 

Any help would be appreciated!

Highlighted
14 - Magnetar

Hi,

Without knowing exactly what your actions and data look like right now, the below is pseudo-code, but the Action tool has a "Update Value with Formula" option.

 

In this example (assuming your dbo.TxnMthTabJUL17 is the "Prior Year"), that formula might look like the following:

Replace([Destination],"JUL17",LEFT([#1],3)+TOSTRING(TONUMBER(Right([#1],2))-1))

Basically what this will do is replace the "JUL17" value with the left 3 characters (MMM) of your Text Input, and the right two characters (YY) with the year decremented by 1.  Note that this logic may not handle your table format correctly in cases where the year in the Text Input is before 2010.  To account for that, you would need to tweak the last bit with a PADLEFT(,2,'0'), as in the example below:


Replace([Destination],"JUL17",LEFT([#1],3)+PADLEFT(TOSTRING(TONUMBER(Right([#1],2))-1),2,'0'))

 

 

I hope this helps!

Highlighted
6 - Meteoroid

Perfect!! Thank you 🙂

Labels