Need SQL Coding help for Alteryx Query Designer

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.

Workflow

Datasets

Developer

Developer Tools

Input

Macros

Date Time

Best Practices

In Database

In-Database

Accepted answers

CharlieS

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"

All comments

CharlieS

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.

20200117-LastMonthQuery.yxmd

RAHULMISHRA

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703