Writing the output data in in different tabs based on the one column.

Question

Hi All- I need help in one of my problem in writing the output in the different tabs of output file.

Below is my dataset where I want to move all data based on the "Inter Company" Column. Output file is already setup with intercompany tab name.

For example, I want alteryx to pick all the data where intercompany is 0001 and paste the data into the output file with tab name as "0001". Similarly, I want all the data to be pasted in their respective tabs based on their intercompany. Point to be noted- I do not expect all the intercompany data available all the time. Hence, where there are no data, my output data tab should be kept blank for those intercompany.

Input data File

Trx CCYLegal EntityNatural AccountSub AccountCost CenterInter CompanyProductTrans TypeBalanceUSDxxxxxxxxxxxxxxx00000100-14.1USDxxxxxxxxxxxxxxx00000100-30JPYxxxxxxxxxxxxxxx0025000040JPYxxxxxxxxxxxxxxx0025000024USDxxxxxxxxxxxxxxx00250000-3116.25JPYxxxxxxxxxxxxxxx00250900110050AUDxxxxxxxxxxxxxxx00308000-1195JPYxxxxxxxxxxxxxxx003500001346USDxxxxxxxxxxxxxxx00350000-2616JPYxxxxxxxxxxxxxxx0805CN9391500125USDxxxxxxxxxxxxxxx007501001160USDxxxxxxxxxxxxxxx0075010041.2USDxxxxxxxxxxxxxxx090052D760600-998.81USDxxxxxxxxxxxxxxx00780200418.2USDxxxxxxxxxxxxxxx090052D789000-355.38

caschecter1 · Answer

I will add a couple of thoughts to @CoG notes.

1) You will have to somehow add a row to your data for each intercompany whose worksheet needs to be overwritten in your output file. Even if the single row, just has the intercompany value and the rest is Blank. Just as @CoG suggested.

2) As for the access denied error, I think that there may be a fourth reason as to why you are having the problem. I've experienced the problem when trying to write to a network drive and the write of each worksheet isn't fast enough. So, it may not be multiple output tools causing the problem, but the problem can occur even within a single output tool writing multiple sheets. I have used the "Throttle" tool to slow things down with some success, but usually when I have multiple output tools connected to a "block until done". I wonder if a batch macro that uses the Intercompany as the control parameter would slow things down enough.

CoG · Answer

Messages like that can occur for at least a few reasons. The three most common that I'm aware of are:

* The file is open on your computer (or on someone else's from a shared drive)
* You have multiple Output Tools trying to write to a single file at the same time. (This occurs because when one Output Tool writes to the file, it locks the file, then the second Output Tool can't access it and throws the error)
* You are Outputting to the same file you are using as Input file. (Same reason as above except now the Input Tool locks the file while reading from it)

Here is the simple sample from above:

Sample Workflow.yxzp

akumar2609 · Answer

Thanks for the quick reply. However, in my excel output file, all the data is not getting copied in their respective tab and getting the below error message. Could you please attached your workflow and through some light on the error as well?

Error: Output Data (2): Unable to create backup of C:\Users
bkjfze\Desktop\Bijay\Testing\4Janoutputfile.xlsx to C:\Users
bkjfze\Desktop\Bijay\Testing\4Janoutputfile.xlsx.bak: Access is denied. (5)