Challenge #460: Fun Facts About LEGO® Sets! Part 2

Weekly Challenge LMS Banner.png

Hello Community members!

A solution to last week’s challenge can be found here.

Once again, thank you, @Qiu , for this fun challenge!

Last week, in challenge 459, you determined which LEGO® set contains the largest number of parts, identified the theme with the largest number of sets, and calculated the average number of parts per set for each year. Now, it is time to switch gears and perform an in-depth analysis on one of the most legendary LEGO sets of all time, the Taj Mahal set!

In this challenge, your task is to determine how many other LEGO sets you can build using only the parts from the Taj Majal set (number 10189-1). Note: A set is considered "buildable" from the Taj Mahal set if the part numbers and colors match.

To complete this challenge, you are provided with three datasets.

Datasets:

sets.csv: Contains information on LEGO sets, including a unique set number, name, release year, theme ID, part count, and image URL.
inventories.csv: Lists the inventories for each LEGO set, which include a set ID, its version, and the set number.
inventory_parts.csv: Details the parts for each inventory, including part number, color, quantity, and whether it is a spare part.

Note: If you get stuck, there is a hints box in the workflow that will help you solve the challenge.

Let the building begin! Good luck!

The Academy Team

Data Sources:

https://rebrickable.com/downloads/

https://www.kaggle.com/code/andycapp29/best-bang-for-your-buck-lego-dataset-analysis/notebook

Challenge460_start_file.yxzp

Challenge460_solution_file.yxzp

Intermediate

Data Preparation

Core

Transform

Accepted answers

All comments

TheOC

Nice little challenge - I especially like that there few a few traps in there that were easy to fall into.

Spoiler

Classic case of using a "join as a filter" throughout.

Unfortunately, the Community still doesn't let me attach photos.

Challenge460_TheOC.yxmd

griffinwelsh

I believe the number of sets in the expected output is an wrong. The issue seems to be that you are finding the completable inventories and then joining those to get the set numbers, but some sets contain more than one inventory. I did a join with the sets in the expected output to show which parts are missing from the 10 improper matches.

Challenge460.yxzp

ggruccio

Solved, but I was off by one - I've looked at the detail...

Spoiler

Set 519-1 asks for 30 of part 3021 in color 14, but the Taj Mahal set only includes 4 in that color. Screenshot 2025-01-21 103338.png

Challenge460_gg_finish.yxzp

Bobbyt23

Fun challenge. I get the right quantity in the output but 2 of mine are different to the answer. Think it may be to do with part versions on some of them.🤔

Spoiler

Weekly Challenge 460 21-01-25.yxzp

griffinwelsh

@TheOC If you join the L and J anchors from your final join on set number you will find that you have 10 set numbers that were present in both indicating there are inventory ids contained within these 10 sets that are not buildable with the pieces from the Taj Majal set.

Spoiler

EdenTP

Spoiler

Challenge460_finished.yxmd

Kenda

Spoiler

Challenge460_start_file.yxmd

Kenda

@TheOC wrote:
Nice little challenge - I especially like that there few a few traps in there that were easy to fall into.
Spoiler
Classic case of using a "join as a filter" throughout.

Unfortunately, the Community still doesn't let me attach photos.

If you click the Camera button

then click into that window that pops up, you can then use Ctrl + V to paste an image onto your post.

balajilolla2

Spoiler

Solution Attached

Screenshot 2025-01-21 092642.jpg

Challenge460_start_file.yxmd

RolandSchubert

Spoiler

Challenge460_solution_rsc.yxzp

alexnajm

Done - but facing similar output issues. Had to download a few previous answers to understand what the logic was trying to get me to do... and still didn't get it so still not sure if fully complete. But I believe my answer is correct and not the provided solution - happy to be proven wrong, example provided in spoiler

Spoiler

for example, set 509-1 should not be included in the final solution as there is Part 3021 and color id 14 is not fulfilled by the Taj Mahal parts
Challenge 460.png

Challenge460_start_file_alex abi-Najm.yxzp

patrick_digan

Spoiler

Challenge460_start_file.yxmd

3I_ATLAS

Fantastic Challenge! Thank you @Qiu !

Definitely has its 'gotchas'... I wonder if you intentionally didn't include 'version' in the solution output! Well done!

I'm glad @AYXAcademy wisely chose to break apart your challenge in installments because if I had this one combined with the prior one, I'd go insane! LOL

I think my solution might be my person highest 'Join' tool usage average per workflow! 41%!!

Spoiler

challenge_460_solution.yxmd

3I_ATLAS

BTW, the provided solution is definitely correct. You just have to keep going back and figure out what you might be missing or where you went wrong.

Fun challenge!

olga_strubbe

Thank you, @Qiu, for a neat challenge!

Here is my solution.

Spoiler

Challenge 460_OS.yxmd

Qiu

@3I_ATLAS
Good to know you are enjoying this one and good catch.
To be fair I did not intentionally "not" to include the "Version" field in the solution. 😁
I did spend a fair amount time to dig related topic and data and get to know more about the Lego.

Qiu

@alexnajm
@3I_ATLAS has pointed out the tricky part 😀
Set 509-1 has Part 3021 with color id 15 in version 5 which is fulfilled by the Taj Mahal parts.

Qiu

Thank you for accepting my challenge idea.

Spoiler

Challenge460_start_file-Qiu.yxzp

alineruizcampos

Spoiler

Challenge460_Aline.yxmd

alineruizcampos

Spoiler

Challenge460_Aline.yxmd

RWvanLeeuwen

here's my take

Spoiler

maybe I should have taken care of the part counts? I got a higher number of sets to build: 47 (which includes the taj mahal itself)

Spoiler

Challenge460_start_file - RWvL.yxmd

Dhrish

Spoiler

Challenge460_start_file.yxmd

fdavy

Here is my answer.

Now I have to find a Lego Taj Mahal

Challenge460_FDA.yxmd

BransonJamesFarley

Definitely had to reference others to be able to match the provided output. Cool challenge.

Spoiler

Challenge 460 pt 2.yxzp

lisahitch95

This is probably my all-time favorite Alteryx Challenge so far - thank you @Qiu

Beware of spare parts, versions (you can build some versions of the same set with the Taj Mahal, but you may not be able to build another version of the same set - only keep the versions you can actually build, not the other versions), and you probably want to use a lot of join tools to filter (Think about not just using the J anchor, but make use of the L and R output anchors!).