Alteryx Designer Discussions

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

Random Sample from Multiple Files

efaherty
5 - Atom

Hi All! I'm relatively new to Alteryx, hoping this is an easy ask.

 

I have 200 excel files that I need to pull random samples from. I am looking for 10 random records from each file unioned together into one output file (so 2000 records total in the output). Is this something I'd use a macro for? All of the files are located in the same folder.

 

Thanks in advance!

4 REPLIES 4
apathetichell
16 - Nebula

Yes. you need a macro. at least one.

 

I'd recommend reading up on how to use a batch macro to pull in multiple files with multiple schema (assuming they have multiple schema). I do not know what you are doing with multiple sheets but that is often a consideration. The sample tool part is easy as you can just connect that prior to the macro output... 

ArtApa
Alteryx
Alteryx

Hi @efaherty - I attached a solution that may work for you. The following elements are important:

 

1) Your workflow would need a batch macro:

ArtApa_0-1625889092344.png

2) Batch macro would read all your Excels:

ArtApa_1-1625889149707.png

3) You also need a Random % Sample tool inside your macro:

ArtApa_2-1625889225013.png

 

Hope that will work for you.

 

danilang
18 - Pollux
18 - Pollux

Hi @efaherty 

 

You only need a macro for this if the schema is different in any of the files.  If they are the same, then you can use the following

danilang_0-1625918921998.png

 

Use a wild card to read all the workbooks from the directory.  Add a random number to each row.  Sort by filename and random number.  Use a Sample tool to extract the first X records for each file from sorted dataset.

 

Dan

 

 

efaherty
5 - Atom

Thanks all for the responses! The schema's are the same in each file so @danilang's solution worked perfectly!

Labels