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?
Solved! Go to Solution.
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
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.
Okay, something like...
"Select * From "+LEFT([#1],1)+TONUMBER(RIGHT([#1],4))-1
The -1 being previous year from that specified by the user.
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.