Weekly Challenges

Damian_WRO · ‎07-24-2023

mlellison · ‎07-25-2023

The challenge is how to join 2 excel with one having integer value, and the other one having string of range value (e.g. Excel 1 value: 2012 match Excel 2 value: 2000-2019)

1.) Text to column: Split 2000-2019 to two integer column (e.g. min, and max)
2.) Append fields: Append Excel 2 to Excel 1
3.) Filter: Keep if postal code >= min AND postal code <= max

Step 2 generate a lots of data, which is not good
Alteryx approach as below

1.) Text to column: Split 2000-2019 to two integer column (e.g. start, and end)
2.) Generate rows: Generate new row for each postal code within the area (e.g. 2000, 2001, 2002...)
3.) Join: Inner join 2 Excel

Much better, as less data generated, but achieve same goal

The challenge is how to join 2 excel with one having integer value, and the other one having string of range value (e.g. Excel 1 value: 2012 match Excel 2 value: 2000-2019)1.) Text to column: Split 2000-2019 to two integer column (e.g. min, and max)2.) Append fields: Append Excel 2 to Excel 13.) Filter: Keep if postal code >= min AND postal code <= maxStep 2 generate a lots of data, which is not goodAlteryx approach as below1.) Text to column: Split 2000-2019 to two integer column (e.g. start, and end)2.) Generate rows: Generate new row for each postal code within the area (e.g. 2000, 2001, 2002...)3.) Join: Inner join 2 ExcelMuch better, as less data generated, but achieve same goal