Sql Query structure

[praneshsapmm]

Dear Community,

I have requirement which is more related to SQL rather Alteryx .

Item	Col1	Col2	Col3	Col4	Col5
A	1	11	22	33	44
B	2	11	22	33	44
C	3	11	22	33	44
D	4	11	22	33	44
A	1	2	3	4	5
B	1	2	1	1	1

Database contains huge data as shown above . Each columns 'Item' must run through the workflow as shown below (just an example but is more complicated) . When i try to pull the data all at a time , my system hangs and takes a lot of time to process . 

Is there a way to process the data by 'item' at a time . Complete the workflow and process the next 'Item' . 

My workflow is shown here : 

Any help is appreciated.  

[morr-co]

Hi @praneshsapmm : have you considered using the Dynamic Input tool? You could configure it to query for each item value one at a time. For example, if your query template looked something like this:

select *
from example_table
where item = 'item'

You could configure the tool to replace 'item' with value of the items being passed in from the data stream.

[praneshsapmm]

Hi @morr-co 

Actually iam trying by visual query builder by using IN DB tools . But no success yet.

Thanks.

[morr-co]

I'm not terribly familiar with the In DB tools but it appears you may need to create a batch macro to accomplish this. The macro would accept the list of items, then query them 1 by 1. I've mocked up what this might look like below. I think the simpler alternative would be to leverage the Dynamic Input too (which also has a visual query builder). 

[praneshsapmm]

Hi @morr-co 

But dynamic input tool might extract all the data from DB into local Alteryx and then process the workflow .

Iam afraid this might take a lot of time . That's why i preferred to use IN DB tools . 

But still do you mind to share a quick snapshot how it looks to use Dynamic Input .

Thanks .

[grazitti_sapna]

Hi @praneshsapmm,

You can create a batch macro for your requirement. You can write your query in the input table as:

select *
from example_table
where item = 'item'

Now using the control parameter and update value you can update the values in the where clause for different items.

I hope I have been able to help you in your query.

[praneshsapmm]

hi @grazitti_sapna 

Thanks for the answer. Indeed it helped . 

But when i run the workflow , all values are extracted from DB instead of the value provided in condition . 

The where clause is not respected . 

Can you let me know how the value in Action -Update value tool need to be provided. 

Thanks.

[grazitti_sapna]

Hi @praneshsapmm,

You will have to pass the values as list box which would be input to the batch macro. The batch macro will then pick each element of the list one by one during the runs. The batch macro will be run for each list box element. 

Here is one very good example on how you can achieve this:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/run-batch-macro-for-each-list-box-elem...

I hope this will help you with the concerns that you are facing.

Good Luck!

[praneshsapmm]

Hi @grazitti_sapna 

Thanks once again . 

Attached is the workflow simulation what i want to achieve . 

I want only the values matching with list box and input to flow further in the workflow . 

But below workflow sends everything in the input . 

Can you help .

[grazitti_sapna]

Hi @praneshsapmm,

I am attaching a sample macro and workflow using input tool. For your use case you will have to replace string(update_tool) in where clause.

Since I am using text input tool so I have used the filter tool separately, but you can directly update the where clause in your input data tool. The workflow will be run the number of times based on the input fed to the macro. I have given two values(A and B) to the Macro and hence the workflow ran for two iterations as shown in the screenshot below:

Similarly based on the list provided by you the iterations would be decided.

I hope I have been able to help.