How to take 20% percentage of records from all the records

[raswitha]

Hi,

I have file with fields Name,Decile ,Green_quality score,orange_quality score and publisher

From this file I need to take 80% records as type "YES" and 20% records as type "NO" , And type "NO" records should be taken in such a way that they are of publisher "green/orange" and quality score "4" and if "green/orange" records are not enough for 20% then we need to take the remaining records from publisher green and orange equally.

As am new to alteryx could anyone please help me in this?

Thank you.

[NicoleJohnson · Accepted solution]

It's not very elegant, but I think this would work:

1. Figure out how many records will constitute a 20% sample

2. Filter for your initial set of records ("Green/orange" and Quality = "4"), then count how many records you have from that set

3. Append the number of necessary records from Step 1 to the count of records selected, and use a Formula to determine how many more records you need

4. Of the non-selected records, create a Record ID for each record, grouped by publisher (so 1,2,3 for Orange, 1,2,3 for Green, etc.)

5. Sort these remaining records by Publisher Record ID (so you'll see 1,1,1,2,2,2,3,3,3 etc.)

6. Add another Record ID, append the needed number of records to complete your sample set, then Filter on Record ID (the second one) <= Needed Records

This should give you a blended set of records from green & orange to make up your full set of 20% sample size.

See attached workflow example! Hope that helps :)

Cheers,

NJ

[jdunkerley79 · Accepted solution]

An adjusted approach based of @NicoleJohnson answer

- Use a formula tool to create a new column (same expression as Nicole's filter)

- Sort on this descending

- Sample the first 20%

[raswitha · Accepted solution]

Thank you very much for the solution...

[NicoleJohnson · Accepted solution]

@jdunkerley79 I KNEW I was overthinking this. Sample tool to the rescue, yet again... :)

NJ

[sahar]

i have almost the same issue but the difference that i have two data set and i have to calculate percentage 5% of rows from the first data set then filter the other data set for example if 5% from first data set equals 6 records then take only first 6 records from the other data set,

i hop you can help,