Alteryx designer Discussions

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

Analytic App - Editing parameter once inputted

Highlighted
8 - Asteroid

I have an Analytic app that requires one parameter, the current database to extract data from. For example, the user would enter 'A2019' and the wizard will update the input tools accordingly to extract from the A2019 database. 

 

However, in this app I also want to extract from the previous year's database, without having to enter a second parameter. So the user would enter 'A2019' and then some tool would calculate the previous database is 'A2018' and then update another input tool accordingly. All my databases follow the structure A2016, A2017, A2018 etc. 

 

Is this possible? Or do I definitely need a second parameter for the previous database?

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Sure this is possible; you can use the 'update value with formula' option to achieve this, but it may be a bit more complex, but essentially you can build out a formula that would be something like...

 

"Select * From "+LEFT([#1],1)+"2018"

Then on the 2nd one...

 

"Select * From "+LEFT([#1],1)+"2017"

 

etc...

 

Do you want to look into this option and post images with your progress as you get stuck, it's really difficult to provide a DB example due to permissions and so on.

 

Ben

Highlighted
8 - Asteroid

Hi @BenMoss ,

 

The problem is I need this to be dynamic and not hard-coded. So in a few years the user would enter A2021 and it would calculate that A2020 was the previous database.

 

So it somehow needs to extract the 2021 from the parameter, minus 1, and add the A to it before then updating the Input tool. I may be over-complicating it here but I am trying to minimise parameters required.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Okay, something like...

 

"Select * From "+LEFT([#1],1)+TONUMBER(RIGHT([#1],4))-1

 

The -1 being previous year from that specified by the user.

Highlighted
8 - Asteroid

Thanks for your help with this!

 

In the end I have used this and got this to work.

 

Replace([Destination], "A2018", LEFT([#1],1)+TOSTRING(TONUMBER(RIGHT([#1],4))-1))

 

Which of course means my database connection is hard-coded as A2018 initially but will be replaced - not sure if that is bad practise! Also needed the TOSTRING function to be able to concatenate.

Labels