This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
We are loading data to a database from a bunch of source files for cleanup & reporting. Some of these tables are heirarchical and need to be written to in a specific order. I haven't been able to get block until done to work in this situation, as we are using the identity field (autonumber) as our primary key and carrying through that integer to the other tables. So Alteryx still will read the input of the table that is used in another step before the output of that first table is complete.
I've come up with a solution that works quite well by using the append tool followed by a filter, using a control input to define the order. Then I have to remove that field in the select tool afterwards and it will write the data to the table.
I am trying to turn this into a macro (more for the sake of learning than out of necessity) but am having difficulties. I believe it needs 3 inputs.
1 - Defined on the instance of the macro tool itself as the 'filter' parameter
2 - Input control, defined on a single table as you see here
3 - The data stream that will be passed through the macro
I am having the most trouble with step 3 - getting a macro input to dynamically accept a data set regardless of # of columns, so that when the input matches the filter parameter, the data will pass through the filter, the appended field will be removed and the essentially untouched data set will flow to the macro output. I hope this makes sense, and I will keep trying as well.
It sounds like the issue is you need the filter and data input to by 'dynamic' so it can accept various table column formats?
You could possibly use the update XML parameter and really define what each filter sees per input? I did this once and it very powerful but also fairly complicated. I know there are some post that talk about updating xml.
Anthony, thank you for the response! I think the goal was actually simpler than what you are showing, though I will have to look into that as it looks very interesting.
The main problem I was having was that all of the data sent into the tool was not coming out the other side. I found the solution by de-selecting "Show Field Map". Now it will pass through all columns to the other side (before it was only sending the mapped column through).
I decided to use two tools for this, and just set a value of 7 possible run cycles in a drop down (I doubt I'll ever need that many). When the Run Value set at the green button matches the red & green Filter Value, the data will pass through the tool.
The issue this helps me work around is say that we are writing an employee occupation to table 1 (a new occupation), but then assigning that occupation as its table 1 autonumber ID to table 2, the employee table. Alteryx reads table 1 input before it is written to table 1 output, so it would assign a null value when writing to table 2.