Dynamic inDB batch macro issue

albert_alaluf

Hi all,

I have a project which has different goals.

- It should be with inDB tool

- IT should be a analytical app

- It has multiple queries

- Queries are dynamic

So let me explain what I did and how it works.

When it runs as analytical app, I can answer multiple input and used in the query as dynamic input. DONE!

I have 20+ different queries, I unioned all. I wrote a batch macro consists inDB elements. So what it does that, when it runs, it reads the values, replaces the placeholders in the queries and then process it. DONE!

Assume that DT is the dynamic part. The problem is the output.

Query Fields

1 A, DT, sheetname (sh1)

2 B, DT, sheetname (sh2)

3 C, DT, sheetname (sh3)

The output is combined as A, B, C, DT and sheetname is the tab names in excel.

The expected output is

Excel tab sh1: A, DT

Excel tab sh2: B, DT

Excel tab sh4: C, DT

All tab names are working.... However each worksheet are returning as combined fields such as A, B, C, DT regardless any query has a result or not.

I cannot provide the workflow at the moment.

Any suggestions?

Albert

I wrote a batch macro which

Batch Macro

In Database

In-Database

Accepted answers

albert_alaluf

The issue no longer exists. I worked with Alteryx and the issue was resolved.

All comments

apathetichell

I do this too - Quick question - you mention that you union all of the queries together - but do you then filter so that you are running the specific query you want?

Quick check to see whats going wrong is adding a dynamic DB Out to take the query out and send that into a macro output - then you can see what's actually being fed into the in-DB tool.

albert_alaluf

I don't filter anything.

I use this in an audit. Each query connects to the same data source, however I use different fields for testing. So, the values I enter in analytical tool are like database name, table name, date etc... which changes in time, but testing stays the same. Instead of to change them manually, analytical method does the job. I use inDB, because I don't need to bring whole data to my desktop, only the exceptions. I believe no need how fast is with inDB. Another reason I use inDB is it provides the final script which I entered in analytical app and i can put in excel easily.

The issue is the result is combined fields.

To answer your question, I cannot filter any query, this is not I want. Another saying, my expectation is, thing this as a batch job;

- Take the first row

- Change the values

- Process it

- Send to excel

then do the second line... If I have 20 queries do this 20 times....

If I do this as 20 independent indb tool, it works, however I should connect 6 questions to each indb tool, 6x20 = 120 times. This doesn't make sense.

I can't figure it out why the output is contributed from each other query?

albert_alaluf

I forgot to add.

If I filter queries one by one within same macro, (running only one line), it works fine. I receive what I need. When I filter with 2 queries, then it will add fields from query 2.

apathetichell

o.k. -without seeing you query syntax and workflow - here's my guess - you have your query set up like this:

select field1 from ... where field1 =

when you go to change your query in the action tool you are inadvertently making two changes to your query - hence the extra field. My suggestion is a dyanmic out in db. see the actual query you are sending when you run the macro. troubleshoot why the syntax isn't what you want when you batch it.

I do pretty much what you are looking to do - so you are on the right track. If you are totally stumped after adding the dynamic out in-db to capture the actual query syntax could you post screen grabs of the configs of your action tools?

albert_alaluf

I use dynamic out in the query because I need it. Please see below, my query and macro. Macro is used to batch the work. In the workflow, I add multiple SQLs which include the queries, I union them, I replace the values as asked in analytical app and send it to macro. It looks like macro process every line in the union, then combines all into one big file. However I need that for the row1 to write to the excel in a tab I tell, and so on.... I'm pretty sure I need to add something in the macro and tell it when the first job finished, do the second one and so on....

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703