Use Output Tool to Create a File Only If File Does Not Exist

[mkeiffer]

Hi there,

I am trying to find a way to build a workflow where my data is output to either an Excel or an Alteryx database but only if the file does not exist.  Is there any way to conditionally execute the Output Data tool, but only if the file does not exist.

I have tried using the Directory tool and the FileExists function, but I am still not getting my desired results.

Ultimately, I am trying to append data to the same output file.  Ideally, I would like to use an Alteryx database for this but you cannot append to an Alteryx database.  I am trying the approach of using a SQLite file type (you can append to this) but if you try to configure the output using SQLite to Append Existing, you will get an error if the table does not currently exist. 

Hopefully, this is not too confusing but if anyone has a solution of how to create a file using the Output Tool, but only if the file does not exist, that would be greatly appreciated!

Thanks in advance!

[mkeiffer]

@ChrisTX  Were you able to access and download my updated workflows?  Let me know your thoughts on the approach and also if the workflows worked for you.  Thanks!

[ChrisTX]

Hi @mkeiffer 

Thanks for posting a workflow without control containers.   One of the goals for my workflow was an automated process.  If you're ok with some manual work, your workflow is definitely easier.

One comment for the second container where you're reading from and writing to the same YXDB file: you may run into write-contention issues.  The Union tool is defined as "conditional blocking", so Alteryx "may" try to write to the output file before the input file is completely read.

Check out The Periodic Table of Alteryx tools
https://community.alteryx.com/t5/Engine-Works/The-Periodic-Table-of-Alteryx-tools/ba-p/64120

I noticed your YXDB file name included "BKPF_BSEG_APPEND".  I'm working on a project now to expose our SAP data to Alteryx.  Which tools are you using? 

We are using IICS (Informatica Intelligent Cloud Services), reading our SAP database directly.  I can describe a few of our challenges with this tool, if you're interested.

Chris

[mkeiffer]

@ChrisTX Thanks so much for the comments and for posting the link to the Periodic Table of Alteryx Tools.  I had never seen that before, so that will be very helpful.  

The solution that I developed for appending the Alteryx tables actually is fully automated and does not require any manual intervention.  In the actual solution where i am using what I posted, I am actually running one workflow as an analytical app and another workflow as a batch macro.  The part where the empty .YXDB file is created in the analytical app and the part where the actual appending takes place is in the separate batch macro.  I was also a bit concerned about not using the Block Until Done tool, but unfortunately, that tool cannot be used inside a Control Container.  So far, I have not had any issues with it not working with the Union Tool but that is something I am keeping an eye on.

One of the biggest challenges that I am facing is I am using Theobald and Xtract Universal to get to the SAP data, but unfortunately, with the Theobald software, you cannot join the BSEG and BKPF tables because BSEG is a cluster table in SAP.  Because of how big the BSEG table is, I had to come up with another creative approach to get the BSEG data but also manage the large sizes of the file.

I would be very interested to hear of the challenges you are facing with the IICS tool and any other interesting Alteryx tips you might want to share.  Thanks, Chris!

[ChrisTX]

We looked at the two available tools to obtain SAP data (DVW and HVR), but chose IICS.  The two main challenges I've seen with IICS are:

• the data type in our data lake sometimes doesn't match the original SAP data type (Date in SAP may be translated to DateTime, etc)
• IICS is missing functionality to correctly place the decimal point. Example for BSAK_PSWBT: SAP "displayed" value = 530,405.00. Value stored in our data lake = 5,304.05.  But this "error" does not happen for every row.  It only happens if the related currency code is a foreign currency.  The old ACL Direct Link tool handled the decimal placement correctly.  But our new IICS tool does not.  And Informatica doesn't have any plans to implement the functionality.

Chris