Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Single output file needed - filtering data from multiple files

Varshaa
7 - Meteor

I have multiple files with 2 sheets (say A and B). The goal is to create a single output file with 2 sheets containing filtered data from A and B of each file.

 

I was able to create a workflow with 2 macros (1 each for A and B) which read and filter data from directory. However, I am not able to create a single output file combining their outputs. To make matters worse, when I try to write an output .xls file for a single macro (say for A), I see it has results in multiple sheets - 1 sheet for each file the macro read. I want the output file to have single sheet for all A and single sheet for all B. Any help with this would be great!

 

Source I used for creating my macro to read input from different files having same sheet name: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Fi...

 

Thank you.

 

 

11 REPLIES 11
DanielMS
Alteryx
Alteryx

Hi @Varshaa,

 

Please see the attached workflow which demonstrates what I believe you are trying to achieve.

 

Dan

 

 

ashissanpui
9 - Comet

Here are the steps to achieve that:

1. Input all data using wild character(*). Include fullpath while importing those files

2. Filter on fullpath which contains sheet name

3. Apply all other necessary filters and output it to a file during this time use BLOCK UNTIL DONE

 

Hopefully this will save you.

Varshaa
7 - Meteor

Thank you for your reply! I'm not quite sure how to go about "BLOCK UNTIL DONE" can you please elaborate?

 

So while playing around, I realized when I save output from within the macro directly, I get exactly what I need (1 sheet "A" in isolation). No duplicate records whatsoever.

 

However, I also have another macro (for 1 sheet "B" so this is configured differently).

 

As I need to combine outputs of both these macros, I created a workflow to use macro A and B. I had to provide input directory again and I suppose this somehow ended up creating duplicates for me.

 

Is there a better way of doing this?

MacroMacroWorkflowWorkflow

Varshaa
7 - Meteor

Sorry for responding late! Thanks very much for your reply and sharing your workflow.

 

The problem I see in using that solution in my case is that you have only 2 csv files as input. I have a set of several input files with the same 2 sheets and I'm basically looking to get an output file with the same 2 sheets but having aggregated and filtered data from all input files.

 

I have provided screenshots of my workflow in my other reply. Would be great if you could please take a look at it and help me.

 

Thank you!

 

Varshaa

ashissanpui
9 - Comet

You can do like this. 

ashissanpui_0-1576672434337.png

Also if you want to set order for your data.

ashissanpui_1-1576672461980.png

Varshaa
7 - Meteor

Thank you, @ashissanpui 

 

While the join did crate a few data sanity issues which I will figure out, I now see data intended for 2 sheets  A and B combined into a single sheet.  Any suggestion on how I can split these into 2 sheets of a single excel file? Or at least split them into 2 files? Thanks!

 

Data for sheet A and for sheet BData for sheet A and for sheet B

ashissanpui
9 - Comet

@Varshaa  after input app, use a formula app for new field source which will identify data stream source

ashissanpui_0-1576740868557.png

 

then at the output file select the following,

ashissanpui_1-1576740914798.png

 

this will create separate excel sheets. Try play with settings to get your desired output.

 

Ashis

Varshaa
7 - Meteor

@ashissanpui This was such an elegant tip! Thanks very much!!

 

It works great when there is no macro used. I will relook at my macro to check where the Source field is getting removed.

ashissanpui
9 - Comet

Thanks Varshaa,

 

You can check the option Keep the field in output. Source will get carry forwarded or else be dropped after processing if unchecked.

 

Labels