For this week’s challenge, you’re taking on the role of a corporate superhero out to save the world one workflow at a time.
The story: Your company, ABC Consulting, has been providing consultation hours to customers over the last year, but an end of year audit shows that many of the consultation hours have gone unfulfilled. Your goals in this challenge are to reissue a new set of coupon codes against the backlog of consulting hours from FY21, issue new coupon codes for Jan FY 22, eliminate the manual process currently used and create an automated communication that goes out to customers.
Currently an employee does the below manual process in order to create coupons codes for customer consultation. She/he:
- Pulls a report of all new orders this month and manually appends them to an Excel sheet
- Filters so orders are in ascending order
- Counts how many coupon codes each customer gets based on $30,000 increments
- Duplicates customer order rows to match the count of coupon codes (so that a row count can be given to this newest batch of codes and added to the current master Excel file)
- Then creates coupon codes with the following format (ABC + Record ID + Order Number + Row Count)
• Ex: ABC13100065001
• After this is complete the employee emails every company’s contact with their allotted coupon codes from this month’s purchases, using the following template.
Email Template: (Please feel free to modify if you see value in doing so.)
Dear [Company Name],
Please, find below the coupon codes available to you this month for your purchase order volume:
Quantity: [Total Number of Coupons]
Codes: [Coupon Code #1, #2, #3, etc.]
Criteria:
• Order Numbers should be filtered into ascending order
• For every $30,000 in dollars spent on orders the customer gets one coupon code for consultation hours
• Customer IDs, order numbers, and coupon codes, should be unique keys
• Both Data Bases provided should be used to cover the totality of backlog of coupon codes and January coupons.
iVBORw0KGgoAAAANSUhEUgAAAXoAAAA0CAIAAAAL52RvAAAABGdBTUEAALGPC/xhBQAAAAlwSFlzAAAScQAAEnEB89x6jgAAABJ0RVh0U29mdHdhcmUAR3JlZW5zaG90XlUIBQAADRxJREFUeF7tnd+OFccRh/0GzhvEb4DfwH4D+w3sN0jewFxxhxIpcGEJCS4s+WKJsYR9w14kYq8WIWE2CgqKMVqL4FUQ8kIisCVL5NtTvZXa6j/TZ2fm7J51fWqNTtdU93QPU7+pnjNneetNEATBSgi5CYJgRYTcBEGwIk5Gbv7y5D83Hu2nSrAOPHny5O7du7u7u6keBMuzarl5/OLn97/49q0/fUNJpmAdCLkJxrNSubn37NVvPv2baM2ycvPjT7+c395DqhCsZApWSMhNMJ6Vys0n2z+o1iwlN1anWIgla7BCQm6C8ayH3Lz96Y62Crk5EUJugvGsh9zYViE3J0LITTCekJugi5CbYDwhN0EXITfBeEJugi5CboLxhNwEXYTcBOMZkJsff/rlj988+3jz+/e/+Na+MkN59/OH2Nl779mr5D3EycoN4zy/vff72/9iLmz5/OWjF2lfMETITTCeAbn54KvvbKjXyjvXHhDAgy/gLSU3f7j3b3SBgq7ZVmgcSmHLoABdffDcaaUW7PSAqibXEuz965P/Skmmxe8wRLlEvJJ1AbvUv92zok2sdvNZ+0mmCnaEM70GGXITjGdAbj7a3HXx2SiEbmpWYSm5sZ7t8t6f/5naZBCHTq2KBZ+GLljNRQTxRGLUIiW5LrB2TmCy1kEN1f+31/6erEffNvrd7SfJmuHm2HPEYxByE4xnQG6463LRE8+EHGJhCxYbD1LaiQatrHOyVrCe7dKQm3Of/yN3luLsRGxqk2GdmXWeKLkBWHkalGCwg+QUJesiv1M7pXZuUSLrttbZzf7+PocAPiRTk9evX4s/JFOdTjdFBsMhUn3Bsp3s7e25TsTCNtVLFH0GW4GM2Z09LNYo1R7Ef0LGPip2CjKh3ORKUSs2RC3ucFRtCsNnF6i1fnJtcsUlFDce7du97d++ow7W2YmFPQmsWPMUjBOuDpTaFMYzq9wQCTdv3rx8+fIFA9XNzU0X7QLGra2tK1euJNdDsOzs7CSnDPG5detWqjd5+PCh+Lspi5HRpnqT27dvi7/t5LPPPsPCNtVLFH0WPV24f/9+qpeQI7JN9QXSUI1S7UFOwsWLFxsyxy5xvnPnTjLVGSs37nKfUG6UpQ6huDAmU0g7jmIVh3hO1qPkckNah8QwknvPXrHNVYA1kTq3Vzc2hXFZEtC/7qW4JRXHZcy6F21KO2ZgPrkhfuR6LcK1zkWfXBdwfWNMuxcQltaC6BTDI+2+cGEwR4BLly6Jc1FuYPBUoIk6qgnlph3808oNzoOjHXSwnFm5sTpiH4g4iFh1oxQ7d3JDVNsHukVc3pTrkWKF6eqD58lqcCfNjtAdZXBUY5hJbrglLi7sA4gHG0gciyQC+8bGRjId1Rr2Wn9SJHqQvcWYlFYwGBtWAWtyM9iJRL4wodwAelpM+kAOqsoiSCtnVBibOBT/cTmrsreYVemJaiig5czKjX2uVEttBKsmjDBZDdaBbnui2uVWRR0Bm7zQc02Viksqd1qKI5+QOeSGa1Qu1sYdG7sNLV1AuZRHwV8UB89kOgSjSlVjIpqVSILjPBetUz+NdU2jk5FyI93WVnOTyw1In6xtncbpHDvXp3A25ebYo/rgq++S1WDlJl/v1LAaUewWbHrSWHO56ciSyvZPitRIoCZhDrmRoILOe6PeS2uRI2j8ODkQI7HBluBJ1gyJLtxkeG7KB10sBsC20YnkZWzzTopS4ij6YAE9CUWxk4G58yP+tZOmp6vxjyui6XqQY6E4ToYanE25cd/pJGsFO6qimhxPbuzX25SiHNgUrD0vt2768OvHttpzTkYyudxoll67UedIatNzfTfCVe/JxXCVvXII6aQoN9pJMYZlajjwIe+kODZH0QcL8EG0DHKZFglwoxLn4lChR27UhxmJRf/5amlmkbMpN2QKtkmyVphJbtAXbUXJ11P2C6zG0yWB3uxTHlsk2ZmbyeVG79Kd1ysRLv498qSPhFJ9gVoat2WJZInMXCngoItFJzL+Y3RSlBJH0QcL8IEjivLmCxyZmlOWRbtRcgMbGxv4XL9+Xao9s8jpkhuu9S8fvTi/vcdNVd6j1eJeojslcmMFguLG7Ir9cmdCuQGrehwoWQ+xe3skw50HKStYRgmTy40EBnSupDQqamFjKYaQWORzcXUgd2x2SQxLRLkpH3RxtBP35EJTm1onPYFa9MEC8lkfUWn8C3JW3bwW7cbKDfOSI+KmTTTZ6WRAbh6/+Pnjze/dJd4op1Nu+su0cuNewLHv1Ljcx71uU8MtqSgr+w8t5pObVB9CL/Fa2FjU2Q5YLPJZcxMbMHID10VWrhRw0MVhJ3oU24m00pdQ8k6KUuIo+mCBVKk8yZKz6k5R7mYpnqsikjMyKhlezz+EoyU35P+1nxrVyhxy4x6CzCo3xee1x5YbsCsg+wWZndS57vdlnPL2vLI8FfPJTecdUqOi5yovhpBYUuUwpHVpJk1IWKQK4uCmfNBF1ommGD2diIVtqpco+mCBVFkgj71B+5ez6k6R+NTOm4wZ3EyL6DeDmgMuRVVuuN86rSEq0AsKd1Suey3Wh2pqX+J4cuNatQ8hWIFg2DrUwZLaH2WM3NjB299J2D7zxzo1mIu2kkL/ad/MzCc3nX2yfBD/HrmR12HBaplYUsWEmazmarrghrdo8f9OdFTiJk00P4Jat2xTvUTRBwukyiHu8fnccqMndqknxEpVbuwPf9ovm6gbpRaxwonIzfgUYIzcuBdwZNHkjJ0PX9x50DLr233K5HKjF26PfAjyrCR/oSZHbvs2y4DF0Y6Eq6yeiGpZmLjwlph3Uz7o4mgn8mAYZ4nbwU70oKleQlo5HyyQKoegp/JIRU7L3HKzlHNOVW7sBd1+Tc56nkK5oSTrcRkjN2Cby5m039M3Xrex2BcCXWn8uHRCJpcbkDhpvL3ikMAevNa5z0vPulASpG2qLCBWxSj+7qF1rhSwcPed6ETYOv+8E1EEmqR6CRHWomqkikGFG5FdS7khpO0F3Y7wfs+VyY1rMvK7m5FyYx/TyM+y7Jqo81mvbUKy6V4sYr7JbzbmkBuJDahFgkPVgTt548GBqpJdSYEYU+UQffzhtAk65QZ0Ii4fgbwTXX/ZNZdFQ9rJnxhT5Sg6C1lbrbfcJGsF67mU3BxvEdEjN278/Q9HioyUG6ZpX+ez6jP4uo3gxEVSJDsqytxLqjnkRtMQqP2emECyQqCBTVw5NQE6vH79ujjkHYo9VQ6RMUDeW7/cLNuJGPHPzycSg529uXIdHLgiNyB9CustN+0I7/c8hnCAC7aedMB9zZy/87IUI+UG3GuHWnpet3HP7HUA2K2Kzb2kmkNuQKMLWIygERxC4LMsT1wYa/ICiMvW1harCbbYtas8VQHZlSoGxAJSxdAvN7BUJ0xHh8oUdnZ2FjPe3dzcFCN7XWoDsitVMji69rlmcuOeFDSyA/fUs60g9sZO6Xxs4bSvs5WL8DEJzni5cVPQIk+O29ijoy+2STHrmYmZ5AaIK1kCFCFc88BjGSJPN3Kw1xYp4pAqHSwlNzWKnUBj1kwhnzLI3lQpoVqwZnID9s75zrUHeWAgSfkbgNxj5e8Hk1DkEZ4/78RTeiYfkReX80WBUzSK/l1ktv2tcCsu3xqHFsbLDeQ/QTjX8bqNE5T8GY0dG0mQnJY5mE9uBDIUUhKCk1s08cYHllHtw9GEENrY2MCZLZ+xpH0lcINU6YAB4O8if5JOFJSRkYvuMGs+17QSeg7NGcg7kYa1nhmbONQGaVnKOacqN/kLrCiI/AcGH3792L74XyvFb6BrP/zRMvib7GIpLiXc2k0KnjIR+UGGnUhtPTKJ3OSDGUy40A67jCrKk1PVkcvGBnPLTfBroCo33PMHpYGCT0ML8nyhtqzQUhSpPC3KS3I9Su2hSa0U059J5CbPtorHsrgTW0u+nJDNtKQKuQnGU5Ub4PrOX2O1hQyImCGQ7MpLi3vQoHBXL/pLqf1pmBuP9hutGguTPE1rlOKAJ5EbsP0MPoFyz7nQlLSjhP1nmmlJFXITjKclNwLXPRKgmQ4xT9hwC7U3Zz4TP+rD1U+QNy56/OmBbulKOqQQUbUbuFBsxYEGv6tiJAyvoVaMvDFgBiZt2Q6ugBowcj3i4JiZqUxQ5pisFThv6kxpn8bjEXITjGdYbs4SxCFxjnxIIaRZ3M2RC+RwFNUaZCtZ14eQm2A8vy65OUHsmm4wWzmFhNwE4wm5WQUsA+13TKvJp6Yl5CYYT8jNKmDhplrz3oiHzSdIyE0wnpCb2bn37JVNbdovXp9aQm6C8YTczAhrqPPbe1Zr1jS1gZCbYDwhNxODoCAr7x79i/FS3q7/x3Wnn5CbYDwhNxPjJEYLWjPH6zArI+QmGE/IzcQ4lZHy0ebu+uY1QshNMJ6Qm4nR957ZfrL9w9UHz9fxa++cly9fPn36NP/zUUHQT8hNEAQrIuQmCIIVEXITBMFKePPmf4GyDTAL6GK4AAAAAElFTkSuQmCC
Weekly Challenge #306
Input
Output
Input 1.yxdb
Input 1.yxdb
C:\Users\e026936\AppData\Local\Temp\Engine_40812_2f493cc719e342c59c2648b3b8bf31e0_\Engine_13572_d45f6b23bbc84d89b9ee5668e898c072_.yxdb
Single
Profile
1001
weeklychallenge@123abc.co.ayx
Livetube
100178
34561
1005
weeklychallen.ge@123abc.co.ayx
Leenti
100777
315264
1006
weeklychallen.g.e@123abc.co.ayx
Avaveo
100778
112051
1008
weeklychalle.nge@123abc.co.ayx
Leexo
100779
12546
1010
weeklychalle.ng.e@123abc.co.ayx
Eadel
100780
3648
1020
weeklychall.en.g.e@123abc.co.ayx
Meedoo
100180
76945
1030
weeklychal.len.ge@123abc.co.ayx
Dynava
98143
31548
1037
weeklychal.le.n.ge@123abc.co.ayx
Gabtype
99123
25198
1040
weeklychal.l.eng.e@123abc.co.ayx
Mynte
100776
94157
1051
weeklycha.lleng.e@123abc.co.ayx
Jamia
100772
106847
1081
weeklycha.l.le.nge@123abc.co.ayx
Eidel
100773
114721
1088
weeklycha.l.l.enge@123abc.co.ayx
Devcast
100775
101549
1095
weeklycha.l.l.e.nge@123abc.co.ayx
Zoomcast
100771
64921
1104
weeklycha.l.l.e.n.g.e@123abc.co.ayx
Flipstorm
100770
124513
1111
weeklych.alle.nge@123abc.co.ayx
Wordpedia
100183
19584
1115
weeklych.all.enge@123abc.co.ayx
Thoughtbeat
99358
156987
1191
weeklych.a.ll.en.g.e@123abc.co.ayx
Babblestorm
100187
115489
1194
weeklych.a.ll.e.ng.e@123abc.co.ayx
Youspan
100774
100215
1200
weeklych.a.ll.e.n.g.e@123abc.co.ayx
Zoomlounge
100185
10301
1219
weeklych.a.l.le.n.g.e@123abc.co.ayx
Fanoodle
100769
100362
1226
weeklych.a.l.l.eng.e@123abc.co.ayx
Skipstorm
100186
62548
1244
w.eeklychalleng.e@123abc.co.ayx
Ooba
99961
123987
1261
w.eeklychalle.n.ge@123abc.co.ayx
Browsecat
100179
31548
1271
w.eeklychall.eng.e@123abc.co.ayx
Skalith
100181
105489
1281
w.eeklychall.e.ng.e@123abc.co.ayx
Tazzy
102109
43156
1291
w.eeklychall.e.n.ge@123abc.co.ayx
Gabcube
100184
60315
1313
w.eeklychal.l.enge@123abc.co.ayx
Babbleblab
100190
130514
1361
w.eeklycha.lle.n.ge@123abc.co.ayx
Brainsphere
100032
54897
1369
w.eeklycha.ll.en.ge@123abc.co.ayx
Mudo
99541
259321
1382
w.eeklycha.ll.e.nge@123abc.co.ayx
Flashspan
100189
120541
1387
w.eeklycha.ll.e.n.ge@123abc.co.ayx
Flashdog
100781
116487
1393
w.eeklycha.ll.e.n.g.e@123abc.co.ayx
Chatterpoint
101241
121612
1398
w.eeklycha.l.lenge@123abc.co.ayx
Topiczoom
101849
103142
C:\Users\e026936\AppData\Local\Temp\Engine_40812_2f493cc719e342c59c2648b3b8bf31e0_\Engine_13572_36b1932853114babbd557c19c8ac4832_.yxdb
Single
Profile
Email Output.yxdb
Email Output.yxdb
C:\Users\e026936\AppData\Local\Temp\Engine_40812_2f493cc719e342c59c2648b3b8bf31e0_\Engine_13572_1465d4cd128d485a86c32ac37ead877e_.yxdb
Single
Report
Output
Warning
All
ByName
Order Number - Ascending
RecordID
1
Int32
6
0
RowCount
Int32
4
1
RowCount <= [NoCoupons]
RowCount + 1
,
Dear [Company:A],
Please, fin...
C:\Users\e026936\AppData\Local\Temp\Engine_40812_2f493cc719e342c59c2648b3b8bf31e0_\Engine_13572_a81afd521a3f43679df5950bbf82278d_.yxdb
Single
Report
Alteryx Educational Asset - Terms and Conditions
Use of this Alteryx asset is for educational purposes only and bound by the conditions set forth on alteryx.com/terms-and-conditions. Alteryx Information may not be reproduced, transmitted or distributed without Alteryx, Inc.'s permission.”
Horizontal
Weekly Challenge 306_Start File_SG