community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More

Output data and re-input it within the same workflow

Alteryx Partner

Hi all,

 

what is the difference between block until done tool and parallel block until done developed by Adam in crew macros? I have a requirement here to write a particular field from incoming stream to database table using output tool and read the same in the next output tool pre sql statement (output 2). Can I expect output 1 to have finished before output 2 pre sql reading it? I don't have output tool straight  after the output 1 in Block until done, I have a select tool and then output tool. So does the output 2 wait till this completely finish? Basically can we assume the block until done tool finish output 1 flow completely till the end and start processing output 2 flow? I was thinking of using block until done for this, but why we have parallel block until done here? Just want to know the difference here. 

 

 

Thanks in advance.

Asteroid

I don't have a direct answer to your question but maybe this link will help.

 

http://community.alteryx.com/t5/Alteryx-Knowledge-Base/Output-a-file-and-re-input-it-within-the-same...

 

Brad

Alteryx Alumni (Retired)

I'll let @AdamR talk to the nuances between the standard Block Until Done (BUD) tool and his parallel version, but the BUD tool is used to ensure that records don't stream through a portion of a workflow until all records are received. Here's the Help description...

 

The Block Until Done tool stops downstream processing until the very last record has arrived. On output, it also ensures that only a single output stream gets records at a time. Subsequent streams will be blocked until all the records are pushed through the first.

 

A common use case for this is reading from an Excel file, doing some manipulation of the data, and then writing back to the same Excel file. If the workflow is still retrieving records from the Excel file, Excel could still think it is open when trying to write and error out. The BUD tool ensures that all of the "read" process is completed, and then allows the workflow to continue with the "write". 

 

In your case, since you are writing to a database table, I'm pretty sure that Alteryx is "handing off" the process to the database instance. Because of this, I don't think you can have a guarantee that the database will be finished with one process prior to the other starting in your Pre-SQL output. 

A thought I have (without knowing your process) would be to see if the Pre-SQL process in Output2 as well as the actually "write" process you are doing in Output2 could all be incorporated in a POST-SQL process in Output1. Here you could be assured that the Output1 "write" process would be complete before anything in the Post-SQL side would be started. Just a thought...

Alteryx Partner

You are right. But I do not think I can utilize the approach mentioned by you, ok, here is what I am trying to do. Its a common use case i guess. I have a set of Ids passed to alteryx for processing, at the end, alteryx has to update the status of the same IDs already existing in database to "Zero" and then insert the newly processed records with active status "One".

 

Since alteryx presql or post sql cannot use the incoming field values, what i am trying to do is, using output1, i am writing all the samples processed in alteryx to a temp table and reading them in output2 presql to update those records status to "Zero" before writing the newly processed records with active status. By doing what you suggested, still there is a chance that the post sql of output1 may update the records written by output2. So i want to make sure that the status update has to complete first before the write process.

Alteryx Alumni (Retired)

So it's been a while since my SQL DBA days so not trying to get the correct syntax :smileyembarrassed: , but could you not do something like this...

 

Write to the temp table in the Output tool.

 

In PostSQL have the following...

1st statement...Update Original_Table set Status_Code to 0 join Temp_Table to Original_Table on CustomerID.

2nd statement...Select Into Original_Table from Temp_Table

 

I'm thinking this would assure you that the writing to the temp table is complete prior to the PostSQL running. And since the PostSQL is done in the context of the database, it should run the two statements serially.

 

Make sense, or am I still not understanding?

Alteryx Partner

Hi. Sorry still I can't use this approach as the temp table only holds the ID (I am going to write only that), but in the original table I need to load many columns, so cant use this "Select Into Original_Table from Temp_Table". This is really a silly one, there should be a way in alteryx to use field values in Pre and Post SQL. I am having hard time to explain this to our customers, their question is very simple, both the field and pre sql within output tool are in alteryx, then how its not possible to access the field value. I raised the same issue one year back and created this in Idea as well, but no response unfortunately. :(

 

Ok coming to the solution for this, finally I had to use macro to feed in the sample ids and replace IN Clause values in Pre SQL. By doing this, I am not using any temp table. Thanks everyone for their suggestion.

ACE Emeritus
ACE Emeritus

I know how to answer this question If i'm understanding you correctly; but to make sure I am.... are you trying to perform a slowly changing type two dimension?

 

Aka if you had a key of 1 in a database table, you want to set the key of 1 to "active flag=0" and then insert a new record and set that active flag to 1?

 

Aka your table used to look like..

 

Key   Loan     Active

1       A          1

2       B          1

3       C          1

 

Then you get a new record which has loan "A" in it, so you want to set key of '1' to active=0 and insert a new key '4' active =1 like so?

 

Key   Loan     Active

1       A          0

2       B          1

3       C          1

4       A           1

 

 

FYI: you can do this without any macro using a combination of in-DB tools and dynamic inputs :-)

 

Atom

This was a problem for one of my workflows as well. I came up with a hack that seemed to do the trick. Hopefully it works for someone else out there too.

 

Please see attached.

Atom

I actually did this "hack" and it worked with Sahrepoint lists.

 

Best to describe: I had header and item level lists, when header was copied, all items had to be copied as well, however unique ID is not available during the execution, hence the header list has to be read 2nd time.

 

Using solution in the flow - I could get it working and waiting until records are written in and then new ID is picked up.

Labels