Alteryx Designer Discussions

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

Use Rounding Up for Sample Percent Tool

marlontalisvistar
8 - Asteroid

Good day,

 

I am having a difficult time figuring how to round up the results presenting the number of records coming from a sample percent tool. Can any one please help me or would show me a sample workflow. 

 

Example:  201 records *.01 (1% from sample percent tool) = 2.01 wherein results should be giving me 3 records

                 412 records *.01 (1% from sample percent tool) = 4.12 wherein results should be giving me 5 records

 

Thanks

8 REPLIES 8
atcodedog05
20 - Arcturus

Hi @marlontalisvistar,

 

Here is how you can do it. You need a batch macro approach to change the sample rows count. I am using data from your previous posts.

 

Workflow:

atcodedog05_0-1627128701331.png

 

1. Using summarize tool to get row count.

2. Using formula tool for calculating 1% and then taking upper limit of the 1% number to get rounded row count. And passing this to batch macro.

atcodedog05_1-1627128826642.png

3.  In macro I am changing the row count in the sample tool.

atcodedog05_3-1627128935397.png

 

Download and place workflow and macro in the same folder.

 

This way you will get the upper limit number of the file row count.

 

Hope this helps : )

atcodedog05
20 - Arcturus

Hi @marlontalisvistar 

 

(Optional)

Here is a packaged workflow of the same above files. If you are using version 2021.1 or above you can open packaged workflow or follow this guide to downgrade the packaged workflow.

https://community.alteryx.com/t5/Engine-Works/Making-Workflows-Apps-amp-Macros-Backwards-Compatible/...

 

Hope this helps : )

marlontalisvistar
8 - Asteroid

Hi atcodedog05,

 

I am sorry if I'll be asking you many questions since I am very new to Alteryx. First the "round sample" output tool, where did you get it. I can't locate it in the menu portion of Alteryx.

 

Second, how can I edit the macro you created? I can't change it like for example from 10% to 1%. The macro output should be showing me 3 records and not 23 records (for "example1").

 

I would also like that each Invoice type be showing its records based on the random sampling. 

Random sampling if for example - 204 records *.01 = 2.04 (round up), thereby will give the 3 records to audit.

                              if for example - 8 records *.01 = 0.08 (round up), thereby will give me 3 records to audit.

 

Please see my screenshot showing for correction.

 

Moving forward for your assistance and clear explanation.

 

Thanks.

 

atcodedog05
20 - Arcturus

Hi @marlontalisvistar 

 

Answers to your questions.

 

1. No this is a custom-built macro you wont find in the palette.

2.You can change the % in the formula tool. Refer the highlighted sections.

atcodedog05_0-1627144773259.png

3. Since you need 1% of records coming from filter tool. You need to add the summarize tool after the filter. Check red highlighted connection summarize tool should be connected from there.

atcodedog05_1-1627144892523.png

 

Hope this helps : )

 

danilang
18 - Pollux
18 - Pollux

@marlontalisvistar 

 

As opposed to using a Macro, which adds an extra level of complexity, you can reproduce the needed functionality in a series of tools in the main workflow.  

 

danilang_0-1627210949337.png

 

Your data is represented by the 1st Generate rows tool.  The summarize tool is counts the number of records in the input data and this number is appended to the data.  The Percent needed Text Input contains the percentage that you want to sample.  This is also appended to the input data.  From here it's a slightly modified randomize algorithm.  Generate a random number for each row.  Sort by this random number and add a record ID called [RandomOrderID].  The final filter is where the modification comes in.  The filter used here is 

[RandomOrderID]<=ceil([Count]*[Percent]/100)

The ceil() function returns the next integer larger than its argument if this input isn't already an integer.  So 1 percent of 201 is 2.01 and the ceil function pushes this up to 3.  

 

Dan

 

 

marlontalisvistar
8 - Asteroid

Hi Danilang,

 

Thanks for sharing your solution. Likewise to atcodedog05 who also shared his ideas.

Maskell_Rascal
13 - Pulsar

Hmmmm... this post looks eerily familiar for some reason. 🤔

 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Rounding-Up-to-Get-the-Number-of-Recor...

 

Also, @danilang we came up with almost identical solutions! Great minds and all that. 😀

 

atcodedog05
20 - Arcturus

I saw the same post before hence i went with a batch macro approach 😅

Labels