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
A bookstore owner would like to analyze why some books were unavailable.
The Dataset reflects the daily slip records of the book available by title, date, and reason why they are or aren’t available. Note that the column “Availability” provides the number of books available for the given date.
For this challenge, help the store owner find out how many consecutive days a book was unavailable and the reason(s) why.
Hints
• Start by flagging the day a book is unavailable
• Group by consecutive days of availability by book so you can filter the unavailable books
• Pick the first and last day of unavailability and count the number of consecutive days unavailable and the reasons
• Pull the first and last date of availability
Weekly Challenge #323
Input
Godzilla
2/15/2022
0
Soldout
Godzilla
2/14/2022
0
No Stock
Godzilla
2/13/2022
0
No Stock
Godzilla
2/12/2022
0
No Stock
Godzilla
2/11/2022
3
Low Stock
Godzilla
2/10/2022
4
Average
Godzilla
2/9/2022
4
Average
Godzilla
2/8/2022
4
Average
Godzilla
2/7/2022
4
Average
Godzilla
2/6/2022
4
Average
Godzilla
2/5/2022
0
Soldout
Godzilla
2/4/2022
0
Soldout
Godzilla
2/3/2022
0
Soldout
Godzilla
2/2/2022
4
Average
Godzilla
2/1/2022
4
Average
Godzilla
1/31/2022
4
Average
State of Union
2/15/2022
25
Reasonable
State of Union
2/14/2022
25
Reasonable
State of Union
2/13/2022
25
Reasonable
State of Union
2/12/2022
0
No Stock
State of Union
2/11/2022
0
No Stock
State of Union
2/10/2022
0
Soldout
State of Union
2/9/2022
0
No Stock
State of Union
2/8/2022
0
Soldout
State of Union
2/7/2022
0
Soldout
State of Union
2/6/2022
0
Soldout
State of Union
2/5/2022
0
Soldout
State of Union
2/4/2022
3
Average
State of Union
2/3/2022
5
Average
State of Union
2/2/2022
5
Average
State of Union
2/1/2022
5
Average
State of Union
1/31/2022
5
Average
Roundup
2/15/2022
0
No Stock
Roundup
2/14/2022
0
No Stock
Roundup
2/13/2022
15
Reasonable
Roundup
2/12/2022
15
Reasonable
Roundup
2/11/2022
15
Reasonable
Roundup
2/10/2022
15
Reasonable
Roundup
2/9/2022
15
Reasonable
Roundup
2/8/2022
15
Reasonable
Roundup
2/7/2022
15
Reasonable
Roundup
2/6/2022
15
Reasonable
Roundup
2/5/2022
0
Damage
Roundup
2/4/2022
0
Damage
Roundup
2/3/2022
0
Damage
Roundup
2/2/2022
0
Damage
Roundup
2/1/2022
3
Average
Roundup
1/31/2022
3
Average
Growth
2/15/2022
0
Soldout
Growth
2/14/2022
0
Soldout
Growth
2/13/2022
0
Soldout
Growth
2/12/2022
0
Soldout
Growth
2/11/2022
4
Average
Growth
2/10/2022
4
Average
Growth
2/9/2022
4
Average
Growth
2/8/2022
6
Average
Growth
2/7/2022
6
Average
Growth
2/6/2022
0
No Stock
Growth
2/5/2022
0
No Stock
Growth
2/4/2022
5
Average
Growth
2/3/2022
5
Average
Growth
2/2/2022
0
Soldout
Growth
2/1/2022
0
Soldout
Growth
1/31/2022
5
Average
C:\Users\A798421\AppData\Local\Temp\Engine_21272_d95c69c5f74d4b1ea26fd1522b280f61_\Engine_20880_d9549e197e3242bcb56bc9f9ce68399f_.yxdb
Single
Profile
Output
Godzilla
2/3/2022
2/5/2022
3
Soldout, Soldout, Soldout
Godzilla
2/12/2022
2/15/2022
4
Soldout, No Stock, No Stock, No Stock
Growth
2/5/2022
2/6/2022
2
No Stock, No Stock
Growth
2/1/2022
2/2/2022
2
Soldout, Soldout
Growth
2/12/2022
2/15/2022
4
Soldout, Soldout, Soldout, Soldout
Roundup
2/14/2022
2/15/2022
2
No Stock, No Stock
Roundup
2/2/2022
2/5/2022
4
Damage, Damage, Damage, Damage
State of Union
2/5/2022
2/12/2022
8
No Stock, No Stock, Soldout, No Stock, Soldout, Soldout, Soldout, Soldout
C:\Users\A798421\AppData\Local\Temp\Engine_21272_d95c69c5f74d4b1ea26fd1522b280f61_\Engine_20880_f303151df8364f7aa2e3e8a5002d2b07_.yxdb
Single
Profile
Date
English
MM-dd-yyyy
DateTime_Out
Convert Date From:
MM-dd-yyyy
BookName
Consecutive Date
Date
10
Empty
datetimeadd([DateTime_Out],1,'days')
datetimeadd([DateTime_Out],1,'days')
BookName - Ascending
DateTime_Out - Ascending
BookName
Consecutive Test
String
4
Empty
if [Row-1:Consecutive Date] = [datetime_out]
then 'Y'
else 'N'
endif
if [Row-1:Consecutive Date] = [datetime_out]
then 'Y'
else 'N...
Simple
=
Availability
True
fixed
2022-06-20 14:37:19
0
0
2022-06-20 14:37:19
2022-06-20 14:37:19
[Availability] = 0
BookName
Grouping
Int32
4
Empty
if [consecutive test] = 'N'
then [row-1:grouping] +1
else [row-1:grouping]
endif
if [consecutive test] = 'N'
then [row-1:grouping] +1
else [ro...
C:\Users\A798421\AppData\Local\Temp\Engine_21272_d95c69c5f74d4b1ea26fd1522b280f61_\Engine_20880_24449167041440f78e4d132bc019e35d_.yxdb
Single
Profile
***The Challenge Output has incorrect ordering of the Reason for Each Day. The ordering is in reverse. My result came back with the matching reason for each consecutive date.
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
Challenge_323_Robin_McIntosh