# 86|ALTERYX – Smarty’s School Supplies **URL:** \ **Category:** Challenges\ **Tags:** excel, beginner, alteryx, audit, database, output\ **Created:** [May 25, 2023, 3:10am UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686 "2023-05-25T03:10:14Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Boston](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/boston/32/75_2.png) [@Boston](https://www.techhub.training/u/Boston)\ **Post date:** [May 25, 2023, 3:10am UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/1 "2023-05-25T03:10:14Z")
_BYU Student Author_: @Boston _Reviewers_: @DylanKing, @TylerBooth, @Christian, @Alex_Garrett _Estimated Time to Solve:_ 25 Minutes We provide the solution to this challenge using: - Alteryx - [Go here to perform this challenge in Excel](https://www.techhub.training/t/86-excel-smarty-s-school-supplies/687) Need a program? Click [here](https://www.techhub.training/t/information-about-software-used-on-techhub-training/138). **Overview** You are a junior associate at Smarty’s School Supplies that offers bulk supplies ordering. Smarty’s has an audit scheduled next week and the auditor has asked that the databases be in tip top shape when he arrives! This means that all of the databases in Smarty’s system should be in third normal form [this link explains what 3NF means!](https://www.dummies.com/article/technology/programming-web-design/sql/sql-first-second-and-third-normal-forms-160848/). Your boss has tasked you with a portion of invoices from the month of September and has asked you to find a way to convert the dataset into smaller tables that can easily reference each other. Good luck! **Instructions** First open the data file and determine what the “key” should be for each table; or in other words, what can uniquely identify each row of data. There should not be any repeat values as primary keys except for the master transaction file which should reference other tables. Make sure to split up the customer’s first and last name into two separate columns as well as the different parts of the address. After you make the four tables, output each one to a separate excel file. Then, upload the four files and output so each excel file is a separate sheet in ONE excel file. Go to this [link](https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Knowledge-Base/Multiple-Excel-Files-to-One-Excel-File-with-Multiple-Worksheets/ta-p/13364) for help on this complex step. **Data Files** - [Challenge86\_Data.yxzp](https://byu.box.com/shared/static/t1nnn7agncee9et454m0dxpyhg1c4497) > **Suggestions and Hints** > > Try splitting the data into four separate groups: Customer, Product, Invoice, and Invoice Item (the master transaction file). Text-to-column can help split the data. If the second part of the solution brings errors, try using four containers to split the solution into parts to sequentially upload the sheets into one file. The “output option” in the output tool should be “Overwrite sheet or Range”. **Solution** > **Solution Image** > > ![Challenge86_Solution1](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/1X/39c2009606668a405f996a2f33d545bab2899ede.png) > > ![Challenge86_Solution2](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/1X/0a5447159788f3f7af6c18d3a2cb3fdd059a5ce3.png) [Challenge86a\_Solution.yxzp](https://byu.box.com/shared/static/jh3q4fu4t62x913q99hd0tzi0p9q9sgx) [Challenge86b\_Solution.yxzp](https://byu.box.com/shared/static/k5xptct4ii21gdojfagcjaot73o39amf) [Challenge86c\_Solution.xlsx](https://byu.box.com/shared/static/28g65j5r71tuq1cpt38km1thhs6k23e9) _Solution Video:_ [Challenge 86|ALTERYX – Smarty’s School Supplies](https://youtu.be/nazDuRaZ7Rw) ---
**Author:** ![Elizabeth\_M\_Wheatley](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/elizabeth_m_wheatley/32/1423_2.png) [@Elizabeth\_M\_Wheatley](https://www.techhub.training/u/Elizabeth_M_Wheatley)\ **Post date:** [November 1, 2023, 11:52pm UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/2 "2023-11-01T23:52:52Z")
[techhub2.yxmd](https://www.techhub.training/uploads/short-url/v4vrHOzK8l0QNzZLqWgo8pOfzDj.yxmd) (23.3 KB) ---
**Author:** ![Lauren\_Garner](https://avatars.discourse-cdn.com/v4/letter/l/43a26b/32.png) [@Lauren\_Garner](https://www.techhub.training/u/Lauren_Garner)\ **Post date:** [November 2, 2023, 2:39am UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/3 "2023-11-02T02:39:31Z")
![image](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/1/13155f2f879c1145b5c3b74be15d3d41b1bd36af.png) ---
**Author:** ![Dylan\_Satteson](https://avatars.discourse-cdn.com/v4/letter/d/ecb155/32.png) [@Dylan\_Satteson](https://www.techhub.training/u/Dylan_Satteson)\ **Post date:** [November 2, 2023, 10:30pm UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/4 "2023-11-02T22:30:20Z")
This was one of the longer ones that I have done but it was fun! Needed some help from the video. ![Screenshot 2023-11-02 162914](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/1/1ad8917c9e8d1935cbbe1fdcef871693675822b2.jpeg) ---
**Author:** ![linnea](https://avatars.discourse-cdn.com/v4/letter/l/3ec8ea/32.png) [@linnea](https://www.techhub.training/u/linnea)\ **Post date:** [November 3, 2023, 12:26am UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/5 "2023-11-03T00:26:50Z")
[WeeklyChallengeX\_Smartys\_School\_Supplies.yxzp](https://www.techhub.training/uploads/short-url/68DcORvrBlgCe6luvT6cGbNjhUW.yxzp) (25.4 KB) ---
**Author:** ![Parker\_Clegg](https://avatars.discourse-cdn.com/v4/letter/p/7993a0/32.png) [@Parker\_Clegg](https://www.techhub.training/u/Parker_Clegg)\ **Post date:** [November 3, 2023, 12:54am UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/6 "2023-11-03T00:54:53Z")
![Challange86Screenshot](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/b/b695ad77fc2a7bfd8abc4825efd34201c328720c.png) [WeeklyChallengeX\_Smartys\_School\_SuppliesParkerClegg.yxmd](https://www.techhub.training/uploads/short-url/q2k5Euw2VKu3rnan23ekJhYH1oB.yxmd) (24.2 KB) ---
**Author:** ![Christina\_Goodin](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/christina_goodin/32/2421_2.png) [@Christina\_Goodin](https://www.techhub.training/u/Christina_Goodin)\ **Post date:** [November 3, 2023, 3:04am UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/7 "2023-11-03T03:04:25Z")
[WeeklyChallengeX\_Smartys\_School\_Supplies.yxmd](https://www.techhub.training/uploads/short-url/gX3B5Y8ZWDSNv9kalq01XeZnpPk.yxmd) (22.0 KB) ![image](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/b/befcd4fbf9c31e94f7113f8153ca8a6838e15431.png) ---
**Author:** ![Adam\_Allen](https://avatars.discourse-cdn.com/v4/letter/a/6a8cbe/32.png) [@Adam\_Allen](https://www.techhub.training/u/Adam_Allen)\ **Post date:** [November 3, 2023, 3:24am UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/8 "2023-11-03T03:24:23Z")
[WeeklyChallengeX\_Smartys\_School\_Supplies.yxmd](https://www.techhub.training/uploads/short-url/9Hhb29TFNPjCvJEHrFiWj967IfM.yxmd) (23.9 KB) ---
**Author:** ![Julianne\_Goodin](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@Julianne\_Goodin](https://www.techhub.training/u/Julianne_Goodin)\ **Post date:** [November 3, 2023, 3:33am UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/9 "2023-11-03T03:33:39Z")
![Screenshot (299)](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/1/1ecc6376020b3c9794442a0959ba59a5e7184737.jpeg) This was my first time using the unique tool! ---
**Author:** ![jeffwint](https://avatars.discourse-cdn.com/v4/letter/j/a88e4f/32.png) [@jeffwint](https://www.techhub.training/u/jeffwint)\ **Post date:** [November 3, 2023, 8:24pm UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/10 "2023-11-03T20:24:56Z")
This was a fun challenge to try out! I learned a lot from doing this! ![image](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/c/c374f5abed624c3915974ead851b7851e3076613.png) ---
**Author:** ![Gabrielle\_Barney](https://avatars.discourse-cdn.com/v4/letter/g/439d5e/32.png) [@Gabrielle\_Barney](https://www.techhub.training/u/Gabrielle_Barney)\ **Post date:** [November 3, 2023, 8:25pm UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/11 "2023-11-03T20:25:37Z")
[TechHub86.yxmd](https://www.techhub.training/uploads/short-url/MECQrB9VdxEweAjgCT5ES9HPNC.yxmd) (23.3 KB) ![Screenshot (98)](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/f/fcdf4043a0d9e0264965bd46cc5d132e93145a14.png) I really like the unique tool, this was also good practice for parsing information ---
**Author:** ![Elise\_Snow](https://avatars.discourse-cdn.com/v4/letter/e/e95f7d/32.png) [@Elise\_Snow](https://www.techhub.training/u/Elise_Snow)\ **Post date:** [November 4, 2023, 1:36am UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/12 "2023-11-04T01:36:28Z")
![Screenshot (5)](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/0/05e4d9f5873eadd4501fb2983087488bd744aafd.jpeg) [WeeklyChallengeX\_Smartys\_School\_Supplies.yxmd](https://www.techhub.training/uploads/short-url/hLVUhcZhCrMJiDnMJnO6SmRKK7a.yxmd) (23.3 KB) This was a great challenge. I had to use the video for some help ---
**Author:** ![Henry\_Molina](https://avatars.discourse-cdn.com/v4/letter/h/439d5e/32.png) [@Henry\_Molina](https://www.techhub.training/u/Henry_Molina)\ **Post date:** [November 4, 2023, 4:30pm UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/13 "2023-11-04T16:30:07Z")
![ALTERYX Techhub 3](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/f/f9c9d5a7ddce5e1c7aad9bbd582dcdb5fc23d1ab.png) Here is my solution to this, it was fun to use RegEx to parse the necessary data for the customer sheet. ---
**Author:** ![Amy\_Blake](https://avatars.discourse-cdn.com/v4/letter/a/57b2e6/32.png) [@Amy\_Blake](https://www.techhub.training/u/Amy_Blake)\ **Post date:** [November 4, 2023, 4:30pm UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/14 "2023-11-04T16:30:31Z")
![Screenshot 2023-11-04 102731](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/9/90db8b72f98eff7a1de64e597241bea672ed25c5.png) ![Screenshot 2023-11-04 102802](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/c/c6b1dc9ce06060669e6453ce746fb832f7f02c3c.png) [challenge86solution.yxmd](https://www.techhub.training/uploads/short-url/dcQ0kuRCFwYSqTQxkHUEfnOa387.yxmd) (21.2 KB) This challenge was a great way to help me learn how to use containers and I actually learned a lot about outputting data to an excel file because I am a very new beginner at Alteryx. Thanks for the challenge! ---
**Author:** ![meganebarton](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/meganebarton/32/1794_2.png) [@meganebarton](https://www.techhub.training/u/meganebarton)\ **Post date:** [November 4, 2023, 4:53pm UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/15 "2023-11-04T16:53:49Z")
It took me a long time to figure out how to output the files correctly but this was great practice! ![Screenshot 2023-11-04 105304](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/3/39de507c44d34ca4c57375224e57a00701b89d64.png) ---
**Author:** ![jdwilliams4](https://avatars.discourse-cdn.com/v4/letter/j/eb8c5e/32.png) [@jdwilliams4](https://www.techhub.training/u/jdwilliams4)\ **Post date:** [November 4, 2023, 6:00pm UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/17 "2023-11-04T18:00:44Z")
![Screenshot 2023-11-04 115951](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/f/f43ca07838fb97669de5d637aec390916eb105e9.png) [Techhub 2.yxmd](https://www.techhub.training/uploads/short-url/g4cID9CNS0JrZHnoKvLDLjuczhN.yxmd) (25.0 KB) ---
**Author:** ![Mason\_Lewis1](https://avatars.discourse-cdn.com/v4/letter/m/50afbb/32.png) [@Mason\_Lewis1](https://www.techhub.training/u/Mason_Lewis1)\ **Post date:** [November 4, 2023, 6:56pm UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/18 "2023-11-04T18:56:23Z")
![image](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/d/dfe422b7d73c1b7c4ce603668edbc77f1afe6e6b.png) [Challenge 86 Smarty’sSchoolSupplies.yxmd](https://www.techhub.training/uploads/short-url/h9wNW5zqYLlm4YgfW8lysbM3WTc.yxmd) (23.9 KB) This challenge was good practice on outputting data into Excel spreadsheets, thanks so much! ---
**Author:** ![Jill\_Davis](https://avatars.discourse-cdn.com/v4/letter/j/d26b3c/32.png) [@Jill\_Davis](https://www.techhub.training/u/Jill_Davis)\ **Post date:** [November 4, 2023, 7:08pm UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/19 "2023-11-04T19:08:47Z")
![Smartys_JillDavis](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/8/89b106175909187e2e12347451993d1ddc9ac786.jpeg) My Solution! ---
**Author:** ![Nicole\_Humpherys](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/nicole_humpherys/32/1084_2.png) [@Nicole\_Humpherys](https://www.techhub.training/u/Nicole_Humpherys)\ **Post date:** [November 4, 2023, 8:11pm UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/20 "2023-11-04T20:11:48Z")
![image](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/d/d276a31c1ef581623ffd05cc91c62a0c87c4a8ff.png) Homework for Class 11.6.23. I had some trouble with parsing, so I used a combination of Text-Column and RegEx-- even though it isn’t efficient, it does the work just fine! ---
**Author:** ![James\_Gerstner](https://sea2.discourse-cdn.com/flex016/user_avatar/www.techhub.training/james_gerstner/32/5479_2.png) [@James\_Gerstner](https://www.techhub.training/u/James_Gerstner)\ **Post date:** [November 4, 2023, 8:54pm UTC](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686/21 "2023-11-04T20:54:52Z")
![image](https://us1.discourse-cdn.com/flex016/uploads/weeklytechchallenge/original/2X/6/642558c0f30199b9054dd9cbbc99907a6e438d5c.png) I used RegEx to create delimiters before doing any text-to-columns. Great challenge! [WeeklyChallengeX\_Smartys\_School\_Supplies.yxzp](https://www.techhub.training/uploads/short-url/o2yfXr258arZIdYvPXfBYCrnicW.yxzp) (23.4 KB) [Next page](https://www.techhub.training/t/86-alteryx-smarty-s-school-supplies/686.md?page=2)