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\PETER~1.ROD\AppData\Local\Temp\Engine_42232_e170bbc99f254a5d8b999ddb5b16e9ce_\Engine_39028_95539b49dd2e4c4baa96a6a8bdcd02c9_.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\PETER~1.ROD\AppData\Local\Temp\Engine_42232_e170bbc99f254a5d8b999ddb5b16e9ce_\Engine_39028_3255a34df2a34dc0869069aa417d19eb_.yxdb
Single
Profile
Date
English
MM/dd/yyyy
DateTime_Out
Convert Date From:
MM/dd/yyyy
BookName - Ascending
DateTime_Out - Ascending
BookName
Start Date
Date
10
Empty
//book in
if [Availability] != 0 THEN ""
//book unavailable 2 days running
ELSEIF [Availability] = 0 AND [Row-1:Availability] = 0
THEN [Row-1:Start Date]
//book first day unavailable
ELSE [DateTime_Out]
ENDIF
//book in
if [Availability] != 0 THEN ""
//book unavailabl...
BookName
End Date
Date
10
Empty
//book in
if [Availability] = 0 AND [Row+1:Availability] != 0
THEN [DateTime_Out]
ELSE ""
ENDIF
//book in
if [Availability] = 0 AND [Row+1:Availability] !=...
Simple
=
Availability
True
fixed
2022-09-02 16:51:41
0
0
2022-09-02 16:51:41
2022-09-02 16:51:41
[Availability] = 0
,
Simple
IsNotNull
End Date
True
fixed
2022-09-02 16:53:08
days
0
2022-09-02
2022-09-02 16:53:08
2022-09-02 16:53:08
!IsNull([End 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
323 - PR complete - 020922