Dynamic Filter and output

[Te_Nahom]

Hello community gurus, I'm new to Altreyx and I can't seem to figure out how I can filter a dataset to smaller tables then have an output for each dynamically. I attached an image below to help visualize the dataset. 

The file I'm using is an Excel file with multiple sheets with different schema, 

OBJECTIVE        

         ==> I want to dynamically separate the dataset per year

         ==> clean the data accordingly and save

I would like to have a table name '2020', '2019', '2018', '2017' etc...

if I use a filter, I will have to create for each year and I will end up with more than 20 different pipelines. it wouldn't make sense to do it that way as it would affect optimization. I have attached the workflow below. any help would greatly help. Thank you!!

amic output. 

[vizAlter · Accepted solution]

Hi @Te_Nahom — Welcome to the community!

Looks that you have created a macro, so can you please attach macro enabled file instead of *yxmd (File Types) and some sample/dummy data?

And, as far as I could understood from your process which you have designed. you need data in separate tabs. If so, then please take a look if this solution (Output in separate tabs) works for you. You need to bring the Output tool to the canvas and configure that.

Let me know if it works or not...

[BradWerner · Accepted solution]

Hi @Te_Nahom,

I echo what @vizAlter says but just wanted to add in a resource that was helpful for me:

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Output-to-multiple-sheets-within-an...

It describes how to do the process that @vizAlter mentioned. Specifically, how to configure the bottom option in the Output tool to output groups of data to separate sheets in one Excel workbook.

[Te_Nahom]

Hello @vizAlter,

Thank you for your quick response. I have attached the macro and the workflow file below. 

==>I have properly imported an Excel file with multiple tabs that have different schemas.

==> I assigned a column to help identiffy which row of the table belongs to which tab in Excel (as shown in the picture, The column "Year" is the tab name)

My goal is:

==>  take the new file and break them into smaller sets by creating a table for each year. and If I update my input file by adding a new tab of "2021", I would like it to create a new output file named "2021" dynamically.

is this possible to achieve? 

[Te_Nahom]

@BradWerner, Thank you for replying. I have checked the links you have posted and it has helpful tips on how to output data to multiple sheets within an Excel file. However, my end goal is to actually upload it to a local SQL Server database and not excel. 

[Te_Nahom · Accepted solution]

Thank you so much @BradWerner, I have went through the article you posted and finally figured it out 

it mentions step by step on how to  Output to multiple sheets within an Excel file 

I added a formula tool and created a new path to a folder. 

Thank you again for taking your time!!!!