Sampling Based on a Data Field

I have a set of data that lists all of the dividend transactions for hundreds of different stocks (each with a corresponding unique CUSIP#), but many of these stocks pay multiple dividends throughout the year, so this data has anywhere from 10 to 2000 entries per stock. If I build a formula and append a "# to Sample" field to that data set, is there anyway I can sample "X" number of dividend records for a given stock based on that value in the "# to Sample" field? Within the standard sample tool, I don't see a sample based on formula. If it let me put N equal to a variable or field (i.e. "# to Sample") then it would be problem solved I guess.

Any thoughts?

That # to Sample would be calculated using a summarize function and then a formula to calculate how many per CUSIP, and then I guess I would run the sample off of the raw data.

Hopefully this makes sense. TIA for your help

Custom Tools

Preparation

Accepted answers

mbarone

When it says the macro has an error in tool #2, you have to look inside the macro and find tool #2, which is the filter tool. The filter tool is looking at LocationLang field, so probably has to do with that. Or, it has to do with the action tool not needing the quotes in the "replace" selection.

Glad you like the macro! If you wouldn't mind, it'd be great if you marked my response as a solution, if you believe it was a suitable solution.

Thanks!

All comments

tcroberts

I'm not sure if there's a way to do it exactly as you've explained, but I've done something similar where I grouped by CUSIP, and then sampled a given percent of the records. This should allow you to sample, lets say 50% of each stock's entries, or 1 for every 5 records. That being said, this uses the standard Sample Tool, and so it will not be possible to ensure you get the exact same sample more than once (set a seed).

If you can give a more precise explanation of what sort of # to sample you'd be looking for, it might be easier to say whether or not its feasible to do it this way.

Let me know if this helps,

Cheers!

mbarone

Hmm.....I might be misunderstanding, but it sounds like a batch macro might work. What you would do is have your table of raw data with a CUSIP# field. You also have a table that lists each CUSIP# uniquely, and the number of random records you want for each unique CUSIP#.

What you would do is take that data that has the number of random records for each unique CUSIP#, and feed it into a "tool" that asks you to identify your CUSIP# field as well as the number of random records field. You run it and the output is a union of each iteration of the CUSIP#-random-records-to-grab combination from the raw file.

Does that sound like what you're looking for? If so, I've attached the guts of it (the module and the macro).

YXMD1.yxzp

jscott24

Your understanding sounds right, I'm taking a look at this now. Will keep you posted on whether its what I need. Thanks for your help!

jscott24

Its more for an audit-type use so it all depends on the dollar value, so since each CUSIP would have varying $ amounts, they would all have different sample sizes. I think the post below is more of what I would need but still digging and testing to see. Thanks a lot for your help and time though!

Ramirenter

@mbarone

Thank you for posting your Alteryx workflow in the comment.

I have just recently started with Alteryx and I am faced with a challenge, that I would need to create an output so that each location has randomly selected 3 different contract numbers. In the attached workflow you can see, that I have file "Records_revB" where my locations and number of records to grab per location data is placed and then I have "test_revB" where I have the data I want to sample.

I have tried to generate sampled data based on field "LocationLang" and "Location", but the latter does not give me any result even if I put the in the macro the filter "[Location]==7201" I get 0 results. Even though it reads in the 79 different groups.
When going with "LocationLang" I get 79*3=237 records but all for the same location I have in the filter tools condition [LocationLang]=="7201 KC KÄRRA".

I have also attached my workflow in the comments, but would very glad if you could be able to identify or explain to me why my workflow is not producing the same results that your workflow is producing effortlessly.

Test.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703