Help with formula

Hi All - I'm hoping someone can kindly help me solve an issue. I have a dataset of 28 records that I need a certain statement against 19 records and a different statement against the rest of them. I have summarised the data to count the number of records and then used a formula tool to work out 2/3 which is 19. I then want a certain statement against the 19 records and another statement against the others. Also I need to join my data back up as when I use the summarise tool, it of course looks all my data and I'm just doing a count of the 'SID' column. I then do a formula tool to do COUNT * 2/3 - I know need to join that data back together but find a way to return the statement I want against all the records. I have attached my data and done and before and after so you can see what I'm trying to achieve. For some reason, I can't show you the the tools I have used as won't let me attach but hopefully my description above is enough with confirming I have used the summarise and then formula tool on my dataset. Thank you for your help in advance!

Alteryx data 01-11-24.xlsx

Datasets

Accepted answers

Calvin6KV

Hi @binsell, not sure if I understood your requirements correctly, but I did a simple attempt to split the records into 2 groups (2/3 and 1/3). The logic applied is similar to what OTrieger mentioned which is using the Record ID tool alone with the Count Records tool. I have attached my workflow so hopefully that can be of some help.

Also, as a proud alumnus of WBS, just want to mention that it is called the University of Warwick, not Warwick University. Small detail. Thanks

Split Rows by CountRecords.yxmd

All comments

OTrieger

@binsell
Have you thought on using RecordID tool. Just add at the beginning and then you can do whatever you want with the data, filter it on bigger than 19 and then join it again and sort it out based on RecordID and then data will be back in order. RecordID, my favorite tool.

binsell

I did think about this but then I got stuck on how I could return my the statement I wanted against the 19 and 9. I haven't been using Alteryx very long so not quite mastered all the tools yet!

OTrieger

You can filter bigger then 19 and for each of the parts work out the data, when both are handled union back. You do not have to transform the data in one go, make it simple, create the 2 different groups and transform them separately, it will ease your process.

Nevertheless you can create 2 different flags, one for each and then with the use of an IF condition apply a different logic for each category. Both ways will work. I think that first it simpler.

binsell

I'm trying to build this as a future workflow you see as the formula is important to work out 2/3 of the data and then bring back the statement I want. Again, forgive me, I haven't used Alteryx for very long so don't understand all of your solution. I think I would need someone to take my data and show me as don't really understand what you mean. I think the fact I'm trying to build this for the future also means it will need to work when the data has a different amount of records as won't always be 28 records.

Calvin6KV

Also, as a proud alumnus of WBS, just want to mention that it is called the University of Warwick, not Warwick University. Small detail. Thanks

Split Rows by CountRecords.yxmd

binsell

Thank you so much for the workflow, much appreciated. It doesn't return that statement that I required but I can now figure this out as this has been a massive help. The detail about the University is for internal use only and trying to minimise the number of characters. Thank you again.

OTrieger

Very good I can see from your post that you know what to do and that is the whole idea, to assist so you be able to solve it yourself

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703