Do you use Alteryx in a language other than English? If so, we want to hear from you! Please help us improve the international experience of our products by participating in this 5 minute survey.

We are updating the requirements for Community registration. As of 7/21/21 all users will be required to register a phone number with their My Alteryx accounts. If you have already registered, you will be prompted on your next login to add your phone number.

Alteryx Designer Discussions

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

Rounding Up to Get the Number of Records

marlontalisvistar
6 - Meteoroid

Hi,

 

My report goal is the randomize the records. The sample percent tool will get the 1% of the records. Case the records is 206 * 0.01 = 2.06, (same for example, 1.2 = should be showing 2 records) hence I should be getting 3 records. Case result is lower than 1, I should always get 1 record and case no record to read then I'll be given no record to view.

 

Daily, this randomizer will be run, thereby records will be updated and appended. I need also a report that will show all the randomized records at the end of the month.

 

I do hope someone will show me the way since I am a hard time solving this.

 

Thanks in advance.

6 REPLIES 6
Maskell_Rascal
12 - Quasar

Hi @marlontalisvistar,

 

This solution should work for you. You can use a Count Records tool to get the total number of records in your data. Then calculate your percentage of sample needed from there, but wrap it in a CEIL() expression to round it up to the next whole number. The original data is then assigned a random number, sorted, and finally assigned a RecordID. We can then Append the Pct of Records needed to the original data and filter to just the RecordIDs that are less than or equal to your percentage needed. 

 

Maskell_Rascal_0-1626971389669.png

 

Attached is a sample workflow for you to try out. I generated 206 rows of data as your example listed in your post. 

 

If this solves the problem please mark answer as correct, if not let me know!

 

Cheers!

Phil

marlontalisvistar
6 - Meteoroid

Hi Marskell.

 

Thank you for your solution. I am very much happy to see it working. I have provided my workflow using your ideas. And I would like to ask again your assistance if it is possible to give me the latest work date every time I run the sample. This is so that I won't be getting again the same sample which I've audited. It is important it won't be repeated again once I got it. Or if you have any ideas, I would appreciate it very much.

Maskell_Rascal
12 - Quasar

Hey @marlontalisvistar - Glad to hear the solution worked for you. Regarding your second request, can you define what you mean by latest work date? Specifically what are you expecting the available candidates for the random sample to be? 

 

I see duplicate Code IDs, Names, Vendor Names, and Client IDs for the same date in the sample data you provided. Is that an issue, or are the duplicates ok, but you just need the latest date for the incoming dataset? 

marlontalisvistar
6 - Meteoroid

Hi Maskell,

 

It is ok to have duplicate in CODE IDs, Name, Vendor Names and Client IDs as long as we have unique Invoice Number. Invoice number should not have any duplicates that is why I have the unique tool used to remove the duplicate records with duplicate invoice number. Everyday we run by work dates that is why records increases. We audit this invoices everyday and we don't want to audit same record number. That is why I am asking if it is possible to give us unique records everyday. If we already get the records for audit, we hope not to get it again. And I am thinking maybe by using its work date might help. I am thinking that when we inserted excel file with work date 07/23/21 for example, and run to get the sample records to audit, it will provide us the records from 07/23/21. However if 07/23/21 has less records and not enough, it will get its sample from 07/22/21. This is my proposal however what is most important is that we don't audit same records since a report is being provided everyday of audited invoices.

 

Please let me know if I am clear or if you have any questions. Appreciate you help so much on this.

 

Thanks

 

Maskell_Rascal
12 - Quasar

@marlontalisvistar

 

Thanks for the additional information. I believe I have a solution for you. 

 

I'm outputting your daily audit information to a SQLITE database that is set to continuously append the results to the bottom. So this report will continue to grow as you run it each day. I'm then taking that same output and using it as an Input in the same workflow connected to a Join with your incoming files. This ensures that anything that was already audited will be purged before the random audit selection happens. I also included a DateTime tool to convert your date field, so I could later sort by this date further down the workflow. This part ensures that you are always pulling the latest invoice date available. 

Maskell_Rascal_0-1627066719259.png

 

 

Attached is a zipped version of the workflow for you to try out. 

 

Cheers!

Phil

marlontalisvistar
6 - Meteoroid

Hi,

 

Just to ask, how did you do the sqlite input. I can't locate the table. From the output tool, it does not create a table too. Please see my screenshot for the error.

Labels