Output to multiple Excel files with multiple sheets

[bobbybalan]

I have a requirement to output data in Excel format in multiple files, each having multiple sheets. I should have 1 output file for each Client and each file should have 4 sheets - Combined, Paid, Denied, Pended. If there is no record found for a Type then a blank sheet should be included. This is the output I am looking for - 

Files --> TestFile_ABC_DateTimeToday().xlsx, TestFile_XYZ_DateTimeToday().xlsx, TestFile_LMN_DateTimeToday().xlsx

Tabs in each file --> Combined (this will combine Type = Paid, Denied, Pended)

                                 Paid (Type =Paid)    

                                 Denied (Type = Denied)

                                 Pended (Type = Pended)

The two items I need help with

1) Dont know how to add a tab that does not have any record. For XYZ, there are no Pended claims. The XYZ file should have a blank Pended tab.

2) Not sure how I can add the Combined tab. It is not a valid Type.

Any help is much appreciated.

The input looks like this -

Client       Type       Claim #

=====================

ABC         Paid       111
ABC         Denied    222
ABC         Pended    333
XYZ         Paid         444
XYZ         Denied     555
LMN        Paid          666
LMN        Pended     777

[mceleavey]

Hi @bobbybalan ,

this is simply a matter of creating all combinations using the append tool. This is called a cartesian join. Once you have determined all combinations you can determine what is missing and simply add them in:

I've updated your workflow and that should work now.

Also, you might need to wrap the actual output in a macro and use the fullpath as the batch parameter (grouped on a sum tool) to prevent the error where Alteryx tries to write to multiple sheets simultaneously and it says the file is in use by another process.

Hope this helps.

M.