Challenge #75: Scrabble Word Finder

Welcome to Week 75!! We have a few people vying for a new shiny badge!!

This challenge is based off the word game Scrabble. In the game of Scrabble, you are given 7 tiles with a letter printed on them. Additionally, a point value is assigned to the letters. The player tries to find the word that is worth the most points. For example, a player may draw the following 7 tiles: G (2 points), A (1 point), R (1 point), E (1 point), T (1 point), V (4 point). It is important to note that the longest word assembled is not always the best word – for example the word of ‘parget’ is worth less then ‘paver’.

The challenge this week is to find the best word (from a provided text file) that can be assembled from 7 provided letters. Use the workflow and the files attached to get to your solution.

Advanced Users and Challenge 75 Racers: The game of Scrabble also provides a max of 2 blank tiles. These tiles are a wildcard and can be used as any letter, but are worth 0 points. While it is unlikely that a single player has both blanks at a single time, we must be prepared for when it takes place. Try to find the highest scoring word using the blank tiles – but be sure that they are not scored!

lettervalues.txt

words.txt

scrabble.yxmd

Parse

Join

Preparation

Data Analysis

Data Preparation

Expert

Difficult

Accepted answers

All comments

NicoleJohnson

Question... you say to make a word from the 7 tiles provided... but there are 8 tiles in the workflow?

JoeM

Good call @NicoleJohnson. I just updated the file!

patrick_digan

Solution attached for the no wildcards case. I'm trying to think about how to attack the wildcards...

Spoiler

I used a very brute force method. I used the append tools get all the permutations. I also tried out a new tool I've built to reference different columns dynamically at the end. This made it slightly easier at the end with less transposing and crosstabbing.

scrabble.yxzp

patrick_digan

Solution attached for the wildcards case.

Spoiler

I just tweaked it a little so that if you have a blank or two (which you would just only enter the 5 or 6 tiles you had into the app), it just adds in all 26 letters. The app really slows down if you have 2 blanks (over 4 minutes on my machine) since it's going through so many appends with a lot of possible letters for those blanks.

2 wildcards case (v,q,t,r,blank,blank,p)

1 wildcard case (b,a,o,blank,t,q,u):

no wildcards case (e,d,g,c,r,a,l)

scrabble_wildcards.yxzp

LordNeilLord

My word that was difficult @JoeM!

I went for the one macro to rule them all approach, it is a horrible workflow and I'm not sure I could explain it back to anyone...just take my word for it...it works!

Spoiler

Part 1

Part 2

Results

@SeanAdams I tip my hat to you sir, congratulations on being the first to get the 75 badge! Well played

*anxiously awaits 75 badge*

challenge_75_LNL.yxzp

NicoleJohnson

Darn it, @SeanAdams!!

But I made it. With a detour to fix my payroll system and to pick up my car from the shop whilst trying to solve it. So, you know. Monday as usual.

Thanks for the killer challenge, @JoeM! Looking forward to see where the climb takes us for Challenge #100

Spoiler

1. I probably made this extra hard on myself by trying to convert that random text file containing letter points into the workflow without just manually inputting them. So that was step 1. And then I had leave to go fix our payroll system.

2. I initially solved for no blanks, and then 1 blank... but figured that I should be able to get to all 3 answers if I did the two blanks the right way, so that's the solution I ended up posting. Dealt with the blanks by appending the existing hand to each of the letters from step 1, using a formula (ReplaceFirst) to replace the first "blank" with the letter it was appended to, and then doing those two steps again for the second blank. And then I had to leave for 30 minutes for an "emergency" team meeting. Suspect @SeanAdams of sabatoge

3. Tokenize Scrabble words & count number of each letter in word. At this point I discovered it was going to take 5+ minutes to run anything beyond this, and I am a frequest-workflow-tester, so that simply wouldn't do... so I added a random sample tool to just give me 1% of the possible words while I was building it out. I also filtered out anything over 7 letters to shorten the list a bit.

4. Joined by letter from Hand source & Word source. Filtered out anything where the count of that letter in the hand was less than the count of that letter in the word. Then I summarized that list (group by ID's, concatenate the letters with no delimiter) to a list of the words that had also been sorted so the letters in the word were sorted alphabetically. This gave me the word Record ID's that had matches based on hand Record ID. I then used Find & Replace based on word Record ID to determine the original word. I then had to leave for 45 minutes to go pick up my car from the shop. #curses

5. To finish it up, I took the matching words, tokenized them by letter, matched the letters to the values in the letter points value datasource, then summarized them, sorted by descending total points, and selected the top 5.

6. Re-read instructions and realize that the blanks don't count for points. Insert new logic at end to only count non-blank letters that were in the original hand. #morecurses

7. Run workflow. Wait 2:29. Marvel that despite an unpleasant number of joins & appends, it was still sub-3 minutes. Ta da!!

Scrabble_NicoleJohnson.yxmd

Ned

Fun problem. I have a solution that runs sub second even with the blanks if you allow me to pre-compute a few tables. Of course it is taking advantage of awful stuff like regex. One example is:

regex_replace(hand, "(.)(.)(.)(.)(.)(.)(.)", regex_replace(p, "(.)", "$$$1"))

another:

regex_replace([Field_1], "(?<=^.{" + tostring(tonumber(left(p,1))-1) + "}).", ".")

I'll post in a few days if no one else comes up with a super fast solution...

MarqueeCrew

Congrats @SeanAdams on your new shiny badge!

Climbing to the summit alone however could lead to a dangerous selfie situation. Hopefully your team will catch you soon.

Cheers,

Mark

vishalgupta

Solution without wildcard
Got to thanks @patrick_digan for the logic for creating all possible letters combination (don't know why I was stuck!)

scrabble_Vishal.yxmd

alex

"The challenge this week is to find the best word (from a provided text file) that can be assembled from 7 provided letters"

I went down a different path than what I have seen posted so far. No macros, finishes in under 2 sec on my laptop, Top 5 for the advanced version verified to @NicoleJohnson's list. I tried verifying to others but had issues downloading or running the workflows. For challenges that don't have answers posted, it would be helpful if others could post screenshots of thier results (like @LordNeilLord did), so we can compare our results without having to download and run everyone's workflows/macros. :>

Congrats to @SeanAdams and others on reaching 75!

Spoiler

Eliminate words with more than 7 characters and split to rows.

I think folks got hung up trying to create every word possible from the hand they were dealt. Since the word had to be in the list provided by @JoeM, I worked on filtering the list to words that contained the necessary tiles and wildcards.

Split Hand to rows, assign point values, if letter not in hand then change value to 0, union workflow.
Join the 2 workflows above to assign points to each letter in the words

Count letters in each word that have 0 points -these are wildcards, if a word contains more than 2 then filter it out.

From the filtered list, now find words that do not contain more than 1 instance of the letters in the hand.

For the records that passed the 2 tests, filter original list, calculate the point values, join original word to workflow, add hand detail, sort, done.

Entire workflow.
With just a couple tweaks, it provides solutions for the other 2 scenarios as well.

Solution for only 1 wildcard.

Week75_AC_1.yxmd

Week75_Advanced_AC.yxmd

Philip

After a couple of false starts, the solution I came up with. Great challenge! Thanks, @JoeM !

In reference to @Ned, it runs in around 1.0 second (0.8 to 1.2 from several runs), pulling from the raw data. And it handles blank

Spoiler

scrabble solution 2.yxzp

jdunkerley79

Pretty sure @ned has a nicer solution but have a regex based one running in about 7 seconds (with no pre computes) will cope with blanks but not double letters...

Spoiler

Now to cope with repeated letters

scrabble.yxmd

Ned

@Philip - that solution is beautiful! Much simpler than mine and no pre-computing necessary. And well documented! Nice!

Philip

I hadn't considered repeated letters, @jdunkerley79. Had to update to handle them. Thanks for pointing out that need.

Spoiler

scrabble solution 3.yxzp

NicoleJohnson

I KNEW there was another way to do this. Average of 0.8 seconds for any number of blanks. Now I can sleep again at night!!

Spoiler

1. Filter for <=7 letters
2. Text to Columns for Hand & Replace "blank" with Null
3. Count number of "blanks"
4. Append hand letters to each word, and then append the # of "blanks"
5. Series of nested ReplaceFirst formulas going through each hand letter column and replacing any letters it finds with "", then if there are fewer letters left in the word than the # of blanks, make the remaining letters in the word = ""
6. Filter for only words that have no letters left
7. RegEx_CountMatches to determine how many of each letter is in the chosen word
8. Append Points to the letters in hand
9. Join Points to Transposed count of letters in each position of hand
10. Calculate total points per letter, summarize points per word, sort descending, and voila.

ScrabbleNew.yxmd

jdunkerley79

Just for pure obsessiveness got my version down to 1.0s for all 3 hands:

Spoiler

scrabble3.yxmd

Simona

Ok, this challenge was a quite hard brain game, but I think I got it... I spent 15 minutes trying to figure out what was wrong in my workflow when I finally saw in a regex I forgot to put the + after \d so it was cutting 10 points value into 1, so I was getting wrong results at the end when it was calculating the points for each word.

Anyway, it was a great challenge, I did not think about generating words before with Alteryx, it is not very easy to do but it is possible and to be honest, I thought I would have spent more time on this, but it took me "only" 2 hours.. ahah

Thanks

Spoiler

This is my workflow, I cannot wait to see the solution next week and see if there is a quicker/ faster way to do it

scrabble_Simona.yxmd

BenMoss

This was a really fun one, thanks @Simona for pointing it to me!

Not quite Dunkerley speed, but I got all three hands in the same workflow (and will hopefully work for any hand and any number of blanks!).

Spoiler

scrabble - BM.yxzp

Phil_L57

Solution for the 7 provided letters. Append, append, append...!!

challenge_75 vPL.yxmd

SeanAdams

@MarqueeCrew wrote:
Congrats @SeanAdams on your new shiny badge!
Climbing to the summit alone however could lead to a dangerous selfie situation. Hopefully your team will catch you soon.
Cheers,
Mark

Thank you Mark!

I'm eagerly awaiting your 350 solutions milestone - that's the true definition of a peak that will be difficult for anyone else to scale! @MarqueeCrew

Not too long now until Inspire EU!

nick_ceneviva

Attached solution for the simplest case with no blanks

scrabble_nickceneviva.yxmd

MinaGO

Forgot to upload this last week, haven't done the bonus rounds yet. Prepped the letters beforehand (except the casing).

Spoiler

Mina Solution.yxmd

Letters.yxdb

estherb47

My approach. Since I wasn't able to jump on this quickly to win the first 75th badge, decided to take my time and work towards developing a Scrabble word app (which will let you include letters already on the board). Not there yet, but almost. Congratulations to everyone, with a special shout out to @SeanAdams, @NicoleJohnson, @LordNeilLord, and so many others who have contributed to our collective success in these challenges!!!!

Took a similar approach to many at the start, and then went a little macro-crazy. Started with cleaning up the word list to include words that are only 7 characters long (since the objective is a high scoring first word). Wrote the output to a yxdb for fastest processing.

Also cleaned up the letters/points/number tiles and wrote that output to a yxdb too.

Here's where I diverge. Then I designed a few macros:

This one generates all possible words from the letters in the hand:

Spoiler

Spoiler:

It's a convoluted iterative macros with cross-joins to generate combinations, and steps to cleanse repeated combinations, and illegal combinations (e.g., if there were 2 a's in the hand, and 3 a's in the combination as a result of the cross joining)

But it works. And I think I finally mostly understand iterative macros.

This one generates point values per word:

Spoiler

Spoiler:

The input is the possible words based on the letters in hand (each word already has a unique record ID). That's parsed into rows. Letters are matched to point values, then summarized per record ID, and output back to the workflow

This one generates possible words, taking into account one or two blank tiles:

Spoiler

Spoiler:

This one takes in words from the word list that didn't match to the exact letters, and every time a letter from the hand matches, it's taken away from the word. If one or two letters are left over after processing, then that word is possible with a wildcard. Multi-row formula is definitely your friend for this kind of approach!

And finally, my workflow that uses all of these macros:

Spoiler

Spoiler:

Hopefully I packaged all of my macros with the workflow. When the app is finished, I'll post it too!

Challenge_75_EHB_solution.yxzp

lminors

Here’s my solution to the 7 letters. Still working on the nasty bonus blank tiles (probably a project for a rainy day). I wanted to build an iterative macro to deal with it all but there are some seriously nice and simple solutions on here.

scrabble_WC75_LM.yxzp

justin_winter

Guess I'm late to the party, but my first pass worked backward from the words to compare against a bank of letters, find the count and points for each letter, then compared that to a match on the input letters. Very little regex, all formula tool and append. 3.5s runtime.

scrabble.yxmd

philipmannering

Think I found a reasonably elegant solution using only 16 tools that do all three cases.

Also went further and created an app where you input your own letters (0 or more including repeats) and the number of blanks (with the choice of whether or not blanks have value). See attachment.

App (left) and PDF output (right)

challenge_75 - Scrabble.yxmd

Scrabble App.yxzp

samjohnson

Not being a RegEx expert I wanted to figure out if I could do this the old-fashioned way: raw inexperience. And I did. This is, to an Alteryx Ace, probably an example of terribly inefficient workflow development. But what the hey? It works and I understand every step.

This doesn't run in sub-1 second times, either, but 1.4 seconds ain't bad.

The most fun was writing the step-by-step, letter-by-letter checks for each word between 2-7 letters long in the "Finding matches based on word length" Tool Container.

Alteryx is quite simply the most fun I've had coding in a long time.

scrabble_samjohnson.yxmd

Ken_Black

Philip,

That is a beautiful solution. Now, all we have to do is set up a secret data transmission system that can allow us to participate in and win the Scrabble world championships! Of course, I'm kidding because we are not cheaters, but I bet there are plenty of unscrupulous people out there that would love to take the chance. Thanks for sharing this because I really enjoyed the quality of the solution, the awesome documentation, and layout.

Ken

ggruccio

WOW! That was a challenge!

challenge_75_gg_finish.yxzp

samN

really, very happy with my solution. One macro to rule them all, with the only input required is how many blanks exist. Runs all three simultaneously in less than a second.

Spoiler

Probably my favorite challenge yet!

challenge_75_scrabble.yxzp

jasperlch

I have used the Combination macro in my solution.

scrabble_JL.yxzp

TerryT

That was so fun!

Challenge_75_TTT.yxzp

JosephSerpis

Challenge Completed

Challenge_75_Joe_Serpis.yxmd

shanakag

Thought to give a try for this challenge.

Scrabble 2.PNG

scrabble.yxmd

scrabble.yxwz

jamielaird

Here's my solution. I wanted to come up with a macro for this but in the end I went with a more straightforward approach using appends.

Spoiler

challenge_75_JL.yxzp

kat

Really cool challenge! mine can handle duplicates, blanks and runs quickly

Spoiler

Challenge #75.yxzp

dsmdavid

Messy, but works. Not fast, but I can wait 3s.

Take the list of letters, process the hand, process words to decide if they can be built with current hand, score those that can and sort. Next time, I should use much more regex...

Spoiler

My solution attached

Definitely a tough one! A lot of thought went into building that little

Spoiler

I went the macro route with an iterative macro (Combos) that generates all unique combinations of a certain length without replacement
Combos

This is then wrapped by a standard macro (ScrabbleTiles) that takes the output of the combos macro and maps the letters of the hand to these combos.

The main work flow manages the words, points and cleans up the hand before calling the standard macro and joining up for the results

Main

I've included the iterative macro separately since it doesn't seem to be have been added to the package

Dan

Combos.yxmc

scrabble_solution_DL.yxzp

bkclaw113

Iterative Letter Combinatiosn.yxmc

scrabble solution.yxmd

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167