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

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
18 - Pollux

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
19 - Altair
19 - Altair

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