PreppinData Challenge 2021 Week 22- Solution

1

Hello,

Here is my solution for #PreppinData 2021 Weekly Challenge 22. I definitely nned to learn on regular expressions.

Context : https://preppindata.blogspot.com/2021/06/2021-week-22-answer-smash.html

Source file : Answer Smash Input.xlsx (13.3 KB)

Easymorph flow : PreppInData 2021W22.morph (8.5 KB)

Hope it helps you,
Nicolas Mieszaly

2

Here is my variant.

PreppInData 2021W22.morph (10.2 KB)

Changes:

  1. Use “Match” instead of the 1st “Cross-merge” + filter. It’s shorter and also cross-merge may produce a very large dataset.

  2. Use “Merge” in the “Left join” mode instead of the 2nd “Cross-merge” + filter by category because it produces identical result but requires only 1 step and also because cross-merge can produce a large dataset, potentially.

  3. Simplify the IF expression. In expressions like “IF(condition, 1, 0)”, the IF function can usually be removed, because the result of the condition is already dual - either TRUE or FALSE. The following “Filter” action in this case would only keep rows with TRUE instead of rows with “1”.

  4. Use the “File path” parameter type for the source file name, instead of the “Text or number” parameter type. The input box for a “File path” parameter contains a button for easier browsing, which makes changing it more convenient.

    image