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

[akumar2609]

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 CCY	Legal Entity	Natural Account	Sub Account	Cost Center	Inter Company	Product	Trans Type	Balance
USD	xxxx	xxxxxxxxxxx	0	0	0001	0	0	-14.1
USD	xxxx	xxxxxxxxxxx	0	0	0001	0	0	-30
JPY	xxxx	xxxxxxxxxxx	0	0	2500	0	0	40
JPY	xxxx	xxxxxxxxxxx	0	0	2500	0	0	24
USD	xxxx	xxxxxxxxxxx	0	0	2500	0	0	-3116.25
JPY	xxxx	xxxxxxxxxxx	0	0	2509	0	0	110050
AUD	xxxx	xxxxxxxxxxx	0	0	3080	0	0	-1195
JPY	xxxx	xxxxxxxxxxx	0	0	3500	0	0	1346
USD	xxxx	xxxxxxxxxxx	0	0	3500	0	0	-2616
JPY	xxxx	xxxxxxxxxxx	0	805CN9	3915	0	0	125
USD	xxxx	xxxxxxxxxxx	0	0	7501	0	0	1160
USD	xxxx	xxxxxxxxxxx	0	0	7501	0	0	41.2
USD	xxxx	xxxxxxxxxxx	0	90052D	7606	0	0	-998.81
USD	xxxx	xxxxxxxxxxx	0	0	7802	0	0	418.2
USD	xxxx	xxxxxxxxxxx	0	90052D	7890	0	0	-355.38

[AndrewDMerrill]

If you are outputting to Excel, then you cannot have a sheet without a name (blank will not work). I recommend using the Formula Tool to add string "Blank" to rows without [Inter Company]. Then in the Output Tool, you can either Append to/Overwrite Sheets based on your needs, while checking the "Change File/Table Name From Field" box in the Output Tool Configuration:

Output Tool Configuration:

[akumar2609]

I think, I didn't explain my query clearly here, From my data set, I want alteryx to pick all the same intercompany (IC) data to be paste it in my output file in the respective tab assigned for that intercompany. So for example,  In above data set, I have two line for intercompany '0001'. So I want these tow lines to be picked and pated in the tab '0001' in my output file. Similarly, for IC 2500, I have 3 lines and it should be pated in tab 2500 in the same output file.  

[AndrewDMerrill]

Yes, the solution I suggested should do exactly that for you. Even though it says change file name, for excel outputs, the system will instead create or update sheets/tabs with the field referenced, in your case [Inter Company]. Based on your sample input, the output should be 1 excel file with 10 sheets/tabs.

[akumar2609]

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\nbkjfze\Desktop\Bijay\Testing\4Janoutputfile.xlsx to C:\Users\nbkjfze\Desktop\Bijay\Testing\4Janoutputfile.xlsx.bak: Access is denied. (5)

[AndrewDMerrill]

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

1. The file is open on your computer (or on someone else's from a shared drive)
2. 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)
3. 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:

[caschecter1]

I will add a couple of thoughts to @AndrewDMerrill 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 @AndrewDMerrill 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.