Output files created / appended to in the wrong order

[Paul_s_Moody]

I’m setting up a workflow to validate data from a pension scheme.

The data file is based on the real data but has been anonymised, either removing, or adjusting personal data and therefore can be treated as generalised data.

The idea of the workflow is to create an output file containing the model points with 2 additional columns containing the validation run date, and a comment giving the validation failure.

The initial container should create (by overwrite any existing file) a new excel spreadsheet containing the headers. This section ends in tool Output Data (6).

The second container tests that the data of birth is between 2 dates, usually 100 years ago to 20 years, but this has been adjusted to 50 and 40 years to force some failures. The end of this section is tool Output Data (12) which should append the data to the already created file.

The third container tests whether there is a “joined the scheme” date. This section ends in tool Output Data (25), which should append the data to the file already created.

There will be many other containers looking at different fields within the data, but this cut down shows the problem I’m having.

I’ve used a Block Until Done tool to try and force the processes to run in the correct order, and added Message tools for ease. These messages seem to appear in the correct order… However, the output is being created in the wrong order with Output Data (12) and Output Data (25) both being run BEFORE Output Data (6).

How do I get the workflow to run the steps in the order I want? i.e. create the file BEFORE appending the data.

[Qiu]

@Paul_s_Moody 
If I am lucky on first sight, we should use this option.

[DavidSkaife]

Hi @Paul_s_Moody 

A quick observation, should output 6 be returning no records? The filter tool is set to filter where Unique Identifier is null, so no data is being written to the excel file. Is that right?

[Paul_s_Moody]

Hi David, I'm expeting output 6 to return no records, but was expecting the headers to be put into the file. (And any existing file replaced with the new empty one)

[DavidSkaife]

Hi @Paul_s_Moody 

Sorry for not replying sooner, i've had a look at this and it appears to be an issue that even with the Block Until Done tool if the output of a section contains no records it writes that last.

You could try having it insert a dummy row (see here - https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Get-all-files-to-write-regardl...) but that might not be the ideal solution for you. Once i have a bit more time i can look at it in more detail and see if i can come up with a solution

[Paul_s_Moody]

Hi @DavidSkaife,

Many thanks for this workaround - happy to use this approach for now.

I'm definately keen to see if you find another approach when you get some time to "play"

To implement the workaround, in the container that is used to generate the headers, I've added a Random % Sample tool to add a random records and then the formula tool to adjust the  message in the validation fail field to label as a "Dummy" rather than a genuine failure. Output (6) is then output 1st containing the headers and the dummry record. The other output tools then run in order, appending the failed records as appropriate.

Thanks!