Challenge #387: LEGO® My Data (Part 2)

Hi Maveryx,

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

This challenge comes to us from @Sebastian_Chaieb2. Thank you for your contribution, Sebastian!

This challenge is an advanced version of our previous Weekly Challenge #365. In this iteration, we will use the same datasets to generate a report; however, this time we will incorporate LEGO model images into our report!

Distinct from its predecessor, in the app you will develop for this challenge, you will incorporate a dropdown menu, enabling users to select a LEGO collection. In addition, a list box will facilitate the choice of one or multiple release years from a list.

The following is a breakdown of the tasks that lie ahead to conquer this challenge:

1. Dataset Integration and Table Creation:

Combine the datasets to construct a comprehensive table with the following columns:

Model Number (This is the Set ID.)
Model Name
Year Released
Number of Parts
Collection Name

2. App Development:

Create a dropdown menu where the user can select a unique collection name:

Use the Collection Name column from the table created in the prior step.
Use only collections with 50 or more model name entries.
Filter out collection names containing periods (.), commas (,), and forward slashes (/).

Design a list box feature for the user to select a single year or multiple years of release:

Use the Year Released column from the table created in the prior step.

3. Filtering and Model Selection:

Use the input data the app user provides to filter your table and select the Model Number(s) corresponding to the user’s selected collection name and year(s) released.

4. Automated Image Retrieval:

Create a batch macro to obtain images for each model from the Brickset website.
Modify the web address using this structure:

Extract page content and implement a filter to identify rows that contain https://images.brickset.com/sets/images/ alongside the [Model Number] field.
Extract the path to image (JPG) and Model Number (as sometimes on the page will have additional pictures attached).
Use a filter to take only the image link when [Model Number] = [ExtractedModelNumber].
Use a second Download tool and download the image as a blob. (If a JPG image gives an error that it is in the wrong blob format, then use payload parameters Content-Typ: application/png and Content-Disposition: attachment.)

5. Final Output and PDF Display:

The final output should be displayed at the end of the app as a PDF file.

Sources:

https://www.kaggle.com/datasets/rtatman/lego-database?resource=download

https://brickset.com/search/

challenge_387_start_file.yxzp

challenge_387_solution.yxzp

Parse

Macros

Apps

Advanced

Difficult

Accepted answers

All comments

AkimasaKajitani

My solution. This challenge contained many tasks.

Spoiler

Sometimes the download is strange.

challenge_387_start_file_AK.yxzp

RWvanLeeuwen

quite a lot of things to do - even posting all the images herein haha. here's my take

Spoiler

index of things
387 what to expect.png

app

Spoiler

I thought pirates were cool when I was younger

workflow

Spoiler

macro

Spoiler

im never comfortable with building out hierarchies with dynamic naming like that so I cheated my ay through this hierarchy :P

batch macro

Spoiler

report

Spoiler

challenge_387_start_file - RWvL.yxzp

Kenda

Spoiler

Fun challenge! I got rid of the duplicates that the sample answer is showing

(last image in this set isn't being converted from a blob using the blob convert tool...)

challenge_387_ks.yxzp

alexnajm

Fun challenge!

Spoiler

I could've done a batch macro, but a standard macro works fine 😊
Challenge 387 macro.PNG

challenge_387_start_file_Alex Abi-Najm.yxzp

mmontgomery

Took a bit to configure it all right!

Spoiler

challenge_387_start_file.yxzp

TonyAndriani

This was a great challenge. Reminds me of some of the tougher ones from a few years ago.

Spoiler

Struggled with getting some of the images -- a number of the image URLs embedded in the downloaded data don't seem to work with BLOB convert even though they display correctly in a browser. After a bunch of runs it seems that every page with a working image used the same format URL, so I skipped the macro and just did the download in the main workflow. I also decided against eliminating all the collections with special characters. Since we had to keep /'s and spaces anyway it was easier to just keep everything.

WC 387.png

challenge_387_TonyA.yxzp

balajilolla2

Spoiler

Solution Attached

challenge_387_start_file.yxzp

ed_hayter

Spoiler

Nice blend of challenges

Ended up batching the reports so its one page per product. If I wanted to spend more time on it I would have liked to bring in a no available image image to replace the null blobs with.

challenge_387_start_file.yxzp

RolandSchubert

Spoiler

challenge_387_solution_rsc.yxzp

olga_strubbe

This was a tough one. I leveraged the solution provided by @AkimasaKajitani . Thank you, Akimasa, for teaching the way to solve it. I usually get confused with the prep data for List Box and Drop Down tools; and this was a great example of how the data needs to be wrangled in this case.

challenge_387_lego my data.yxwz

Gawa

Download tool enlarges the ability and capability of Alteryx, I like it.

Spoiler

challenge_387_start_file_gawa.yxzp

benjimoser

My Solution. I actually did it without a Batch Macro. No idea why this was mentioned in the challenge description. There also seems to be an data input quality issue (e.g. Star Wars Models from 2014 are not listed on brickset.com).

Spoiler

challenge_387_Benji.yxzp

JamesCharnley

Spoiler

Nice tougher challenge

Challenge 387 Zipped.yxzp

patrick_digan

Spoiler

challenge_387_start_file.yxwz

ahsanaali

This challenge is great for learning/revising interface tools, chained apps, download tool and batch macros. Learned and copy the interface technique between List Box and filter tool from @AkimasaKajitani (very elegant solution)

I opted for Chained Apps to solve this challenge. Here are the screen shots;

Spoiler

App 1 - Selecting a Collection
Collection selection.png

Spoiler

App2 - Selecting Year released (only years relevant for Collection will show up for selection)

Year released selection.png

Batch macro to download the images

challenge_387_LEGO_catalog.yxzp

DaisukeTsuchiya

Spoiler

challenge_387_start_file_DT.yxzp

Sebastian_Chaieb2

As officially Solution is attached, I am posting my workflow/App when I was designing this Idea.

I was so queries how other Aces and great experts will solve it. I believe that reviewing all posted solutions I will learn a lot how it can be done with simpler/better way. Congrats @AkimasaKajitani for being the first one who posted ready solution. Amazing

Thanks you all

My Solution:

Spoiler

Idea_SC_Lego_catalog.yxzp

rmassambane

387!

challenge_387.yxwz

ggruccio

A bit difficult to follow on the download image part, but thought it thru! I like the way my report came out!

Spoiler

challenge_387_gg_finish.yxzp

philipmannering

Solution attached...

Spoiler

@patrick_digan Can you use '%Question.List%' in the Filter Tool → [Year] IN ('%Question.List%') ? I could not get this to work.

Challenge 387 - Lego My Data (Part 2).yxzp

patrick_digan

@PhilipMannering You sure can! You would either need to change your filter tool or your listbox. The listbox has the ability to specify the string that is sent over, so you just have to make sure the quotes match.

Change filter:

Since the listbox already has the quotes, you don't need any quotes in the Filter tool.

Change listbox:

If your filter already has the beginning and end quotes like you mentioned [Year] IN ('%Question.Year%'), then the listbox no longer needs beginning and end quotes (just the separators). You can then test something like 2014/2015:

Yoshiro_Fujimori

As my workspace cannot use Download Tool, I can only follow up to Step 3.

I will do the rest from my home pc.

Spoiler

Workflow

App screen
App Results

challenge_387_YFujimori.yxzp

hdesai7

One of the toughest I have had the pleasure of working through!

Spoiler

Spoiler

challenge_387_start_file_HDesai.yxzp

tammybrown_tds

Macro practice is a wonderful thing.

Spoiler

challenge_387_tbrown.yxzp

alacoume

This one is really hard!

challenge_387_start_file.yxwz

Challenge_387.yxmc

AkimasaKajitani

I made the new workflow using Python tool for resolving the Blob error.

Spoiler

I use the Python package "Pillow" for this solution.

スクリーンショット 2023-10-14 150125.png

I think when the blob error occures, the jpeg file with some trouble is downloaded. But other Windows application can load those jpeg files. So I load them by using Python package "Pillow" and then save as "Correct" jpeg files using Pillow again. After that, I load those "Correct "jpeg files with no error by Image tool. But if we use the Python tool in Batch macro, the workflow is very slow, so I don't use the Batch macro for this solution.

from ayx import Package
Package.installPackages(['Pillow'])


from ayx import Alteryx
from PIL import Image

df = Alteryx.read("#1")
image_list =[]

print(df['DownloadTempFile'])

for img in df['DownloadTempFile']:
    if img is not None:
        with open(img, mode='rb') as f:
            image_file = Image.open(img)
        image_file.save(img+'.jpg', quality=95)
        image_list.append(img+'.jpg')
    else:
        image_list.append(None)
        
df['image_path'] = image_list

Alteryx.write(df,1)