Retrieving the Oracle Data based on the filter

[saiirangam]

Scenario:

If the Amount Value for Current Month Beginning Date (from the Prior Month Report Output) = Amount Value for Prior Month Ending Date (From Oracle Database), Then No Action required, else hit the Database Query from the Dynamic Input Tool and re-generate the Prior month Report.

Current Logic: Retrieving whole Data from the Database around 50k Records and filtering out based on the above Condition. 

Challenge: My Current Logic is taking too much time if the amount is matching, and we are retrieving and excluding the 50k Plus records which is not necessary. 

is there any other way to hit the Dynamic Input tool and run the Query Inside only if the Filter output is "Generate prior month Report." else no need to retrieve data from dynamic input tool?

This would save around 5-10 mins of runtime. 

[Yoshiro_Fujimori]

Hi @saiirangam ,

If I understand correctly,

Current Month Beginning Date is on local PC

Prior Month Ending Date is on Oracle DB.

So "Generate prior month report" can be decided only after accessing Oracle DB.

If this is the case, I guess Dynamic Input tool cannot contribute to the processing speed.

Does the Oracle table happen to have "date of last balance update" for each row?

If so, you may be able to reduce the rows on Oracle side by filtering out the rows with no balance update for the prior month.

Also (if I read correctly) I feel 5-10 minutes of runtime for a job running once a month is not too bad.

and it may not pay for the time for you to struggle.

[saiirangam]

Hi @Yoshiro_Fujimori , Thank for your response.

To Reiterate 

Current Month Beginning Date is on Oracle DB.

Prior Month Ending Date is on Share Drive.

Based on the Scenario, If DB Amount is Matching with Amount in the Local file, then we Can stop the file hitting another 2 Dynamic Input files having Another Oracle Codes.

Also (if I read correctly) I feel 5-10 minutes of runtime for a job running once a month is not too bad.

and it may not pay for the time for you to struggle. - We have Around 200 Jobs in the server with similar workflow structure, where we are unnecessarily retrieving the data and excluding all the rows if amounts are matching.

Attaching the Screenshot from one of the logics, Filter Condition if the Difference value!= 0, right now this workflow will hit the 3 Dynamic Input with SQL codes which are independent, it generates around 40k to 50k records and after that it will exclude all the rows if the amount is =0 in the from the filter condition. so, in this case if the values are matching there is no need to hit the SQL codes to fetch the data. as per the logic the jobs send an email saying, "Amounts are matching New report is not required to generate. "

[Yoshiro_Fujimori]

Hi @saiirangam ,

Thanks for the explantion.

Your case sounds similar to the case in this post.

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/How-to-Dynamic-In-DB-filtering...

"how to dynamically filter data from a database with some other information

like data from a list/file on their computer

without losing the performance of the In-DB functionality."

You may want to give it a try.

As I cannot touch your database, I wish you a good luck.

[saiirangam]

@Yoshiro_Fujimori   Thank you for time, The link you provided is totally different than my challenge.

[apathetichell]

Dynamic Input In-DB. You don't lose any In-Db performance and your query performs conditionally 

[saiirangam]

Here is detailed explanation and screenshot of my workflow.

I need to skip the workflow (the bottom container from the screenshot) to execute if the summary tool output is equal to zero without changing the workflow into Analytical application or Macro.

I am sure there should be some dynamic way, i tried multiple options which are not feasible 

[apathetichell]

My suggestion would be to put the entire bottom container in a macro. Add a filter tool from the summarize tool. Condition met from filter  tool leads to the macro (add a dummy macro input with a text box to nowhere). connect your macro output and your condition met from filter tool to your append data.

I would use dynamic input-in db but the above is easier. the dummy input to allow triggering is key.