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

Need SQL Coding help for Alteryx Query Designer

RAHULMISHRA
8 - Asteroid

 have interfaced HANA DB of my company to In connect DB.

IN HANA Table, One of the view period is mandatory so I have put Codes in SQL editor of Alteryx as :-

 

FROM "_SYS_BIC"."acs.Inventory.InvSummary/QV_INV_DETAIL"('PLACEHOLDER' = ('$$IP_CC$$', '''ALL'''),
'PLACEHOLDER' = ('$$IP_PRCTR_SBU$$', 'PLACEHOLDER' = ('$$IP_SOURCE_SYSTEM$$',
'''ALL'''), 'PLACEHOLDER' = ('$$IP_PLANT$$', '''ALL'''),
'PLACEHOLDER' = ('$$IP_PRCTR_SBG$$', '''SPS'''),
'PLACEHOLDER' = ('$$IP_FISC_PER$$', '2020001'))

 

Instead of above value of '2020001', I want to put a formula which will give previous Month. For current month formula I have put  'PLACEHOLDER' = ('$$IP_FISC_PER$$', ''CONCAT(CONCAT(YEAR(CURRENT_TIMESTAMP),"0"),MONTH(CURRENT_TIMESTAMP))')) and it is working. 

Please suggest a formula so that it will see current system date and convert it to previous month.

5 REPLIES 5
CharlieS
17 - Castor
17 - Castor

While this could be solved in SQL, how about we use Alteryx tools to solve this since we're on the Alteryx Community forum?

 

I recommend to use a Formula tool to create a replacement string for "2020001" and then use a Dynamic Input tool to replace that string when the query is submitted.

 

Replacement String:

First off, I'm a little confused by the extra '0' as I would expect "202001" to represent this month. If this is the case, then to build this string for the previous month from today ("201912"): use the following expression:

 

DateTimeFormat(DateTimeAdd(DateTimeToday(),-1,"month"),"%Y%m")

 

Dynamic Input:

Set up your query in the Dynamic Input as you would a normal input using the "Edit:" button at the top of the tool configuration. Then, use the "Modify SQL Query" option to select the "Replace a Specific String" feature so "202001" can be replaced by the last month string value you've just created.

 

I've attached an example workflow to give you an idea how this all might work. It has a few placeholders and doesn't run since we won't have the same connections, but it should give you an idea how to get this working. 

RAHULMISHRA
8 - Asteroid

In Our company's hana DB, the table from which i am extracting data has fDate format as "YYYY0MM".

CharlieS
17 - Castor
17 - Castor

Got it. Just update the expression to the following:

 

DateTimeFormat(DateTimeAdd(DateTimeToday(),-1,"month"),"%Y")
+"0"+
DateTimeFormat(DateTimeAdd(DateTimeToday(),-1,"month"),"%m")

 

The current result is "2019012"

RAHULMISHRA
8 - Asteroid

Hi @CharlieS,

 

Thanks for your reply. Please see enclosed pic for details. First pic Named as "workflow pic" shows my Alteryx workflow. In this, There are 2 Connect in DB tool am using from ALteryx. The top one is pulling current month data and is working perfectly fine. 

The second In DB too(also like first one) has written SQL code in Alteryx Query designer which can be seen in second pic named as "SQL Code". Now in Second pic,

you can see that I have mentioned :-

FROM "_************"('PLACEHOLDER' = ('$$IP_CC$$',
'''ALL'''),
'PLACEHOLDER' = ('$$IP_SOURCE_SYSTEM$$',
'''ALL'''),
'PLACEHOLDER' = ('$$IP_FISC_PER$$',
'CONCAT(CONCAT(YEAR(DATEADD(MONTH, -1, CURRENT_TIMESTAMP)),"0"),MONTH(DATEADD(MONTH, -1, CURRENT_TIMESTAMP)))'))

these placeholders are  mandatory to retrieve data from HANA. Now, My highlighted code is also for retrieving previous month data in format 'YYYY0MM'. Unfortunately, it is not working. Is there any way do this in Alteryx? will your dynamic input work in my case? Or can you help me with new SQL COde instead of  ('$$IP_FISC_PER$$',
'CONCAT(CONCAT(YEAR(DATEADD(MONTH, -1, CURRENT_TIMESTAMP)),"0"),MONTH(DATEADD(MONTH, -1, CURRENT_TIMESTAMP)))'))??

Thanks.

 

Rahul Mishra

CharlieS
17 - Castor
17 - Castor

@RAHULMISHRA wrote:

Is there any way do this in Alteryx? will your dynamic input work in my case?


Yes. See the Dynamic Input suggestion above. This way you can use the Alteryx date time capabilities to determine the value in a Formula ahead of the query so you can skip the calculation within the query and just replace the value. 

Labels