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\DE88520\AppData\Local\Temp\Engine_14848_28638e157e5249fbb33b048471f8789e_\Engine_14012_dc47403a732046509d6d7da35b572b7d_.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\DE88520\AppData\Local\Temp\Engine_14848_28638e157e5249fbb33b048471f8789e_\Engine_14012_46be4b297d3d4fd6b7576b0b78f33b4c_.yxdb
Single
Profile
BookName
indexing
Int16
2
Empty
IF [status] = "newly unavailable"
THEN [Row-1:indexing]+1
ELSE [Row-1:indexing]
ENDIF
IF [status] = "newly unavailable"
THEN [Row-1:indexing]+1
EL...
Simple
=
Availability
True
fixed
2022-06-14 11:48:34
0
0
2022-06-14 11:48:34
2022-06-14 11:48:34
[Availability] = 0
BookName
status
String
20
NULL
IF ([Availability]=0 && [Row-1:Availability]>0) || ([Availability]=0 && IsNull([Row-1:Availability]))
THEN "newly unavailable"
ELSEIF [Availability]=0
THEN "unavailable"
ELSE "available"
ENDIF
IF ([Availability]=0 && [Row-1:Availability]>0) || ([Availabi...
BookName - Ascending
# of Days - Ascending
Start Date - Descending
C:\Users\DE88520\AppData\Local\Temp\Engine_14848_28638e157e5249fbb33b048471f8789e_\Engine_14012_56265aa50bac424ba5936c8466b385e3_.yxdb
Single
Profile
BookName - Ascending
Date - Ascending
Date - Descending
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_start_file