Dynamically output query results from multiple views in .qvx format

[Aditya_Pillai]

Hi Alteryx Family! I am stuck with a task where i have to output the query results of multiple views built in IBM DB2 into seperate .qvx files INPUT - I have a column in an excel file with the view names i need to query Approach - I have created a Macro which takes in the view name column and updates the view name in the SELECT * Query inside the input tool. This appends all the results from all the views in a single .qvx file. However the requirement is to create seperate.qvx file for each viewname. The Idea which i have is to append a new column named View_Name having the viewname itself within the output and then tell the output tool to output based on the View_Name Column. However i am unable to dynamically add each view name to the output. Any help would be greatly appreciated!.

[Prometheus]

If you can take the view name and turn it into a column, you can dynamically create *.qvx files based on these view names. 

 

 

 

[Aditya_Pillai]

Hi @Prometheus Actually there is a column in the excel which is called View names which has the name of the views in the DB. I wanted to query (doing a select *)each of these view name and output the results of the view in seperate .qvx files

[apathetichell · Accepted solution]

Your query should be in a batch macro . you append the query name/column from excel after you retrieve your query in-db. you then create your formula tool and your file in the batch macro.

[Aditya_Pillai · Accepted solution]

Hi @apathetichell

Thanks for your solution. However I tried a different approach and it worked.

Steps.

1.) Drag Input tool and connect it to the excel file.

2.) Create a batch macro with the input tool and connect to the DB. In SQL editor i typed the following query -

SELECT *, 'Name of the View' as View_Name

FROM 'Name of the View'

3.)Update the action tool to replace the string ' 'Name of the View' and configure the control parameter to iterate over view_name column from the excel file

4.)Connect Output tool inside the macro and output based on View_Name column acquired from the query and select .qvx as extension. Select Change path/Entire File Name

5.)Connect the macro to step 1

Please feel free to validate my solution. Thanks again for your response appreciate it.