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
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.