Challenge #323: Analyze books Availabilities

[JamesCharnley]

Spoiler

Nice and straightforward!

[Steve_Vincent]

Spoiler

 

 

[ddiesel]

My solution:

Spoiler

[rdean44]

Solution Attached:

[michaeljp]

Parsing and Sorting applied differently here

[acarter881]

Here's my solution.

A few comments:

1. The dates in the sample output should be converted to date format (see #2).
2. The Reason for each day field in the output has an incorrect ordering. For example, the reason Godzilla was sold out on 02/15/22 is Soldout, but the sample output would have you believe the reason was No Stock.

Spoiler

[DataNath]

My solution to #323.

Spoiler

[mikekuznetsov]

In this, I think QXuery could offer a more elegant solution. This script uses Window function and is quite compact:

xquery version "3.0";

let $docSorted := for $i in doc("AlteryxChallenge_323.xml")/dataset/record
                  order by $i/BookName, xs:integer(tokenize($i/Date,"/")[1]), xs:integer(tokenize($i/Date,"/")[2])
                  return $i
return
<table>
{
for tumbling window $w in $docSorted
  start $s previous $s-prev when $s/Availability = "0" and ($s/BookName != $s-prev/BookName or $s-prev/Availability != "0")
  end $e next $e-next when $e-next/Availability != "0" or $e/BookName != $e-next/BookName
return 
  <window>
    <BookName>{data($s/BookName)}</BookName>
    <StartDate>{data($s/Date)}</StartDate>
    <EndDate>{data($e/Date)}</EndDate>
    <NoOfDays>{count($w/BookName)}</NoOfDays>
    <ReasonsByDay>{string-join($w ! Reason,",")}</ReasonsByDay>
  </window>
}</table>

Here is the resulting xml:

<table>
  <window>
    <BookName>Godzilla</BookName>
    <StartDate>2/3/2022</StartDate>
    <EndDate>2/5/2022</EndDate>
    <NoOfDays>3</NoOfDays>
    <ReasonsByDay>Soldout,Soldout,Soldout</ReasonsByDay>
  </window>
  <window>
    <BookName>Godzilla</BookName>
    <StartDate>2/12/2022</StartDate>
    <EndDate>2/15/2022</EndDate>
    <NoOfDays>4</NoOfDays>
    <ReasonsByDay>No Stock,No Stock,No Stock,Soldout</ReasonsByDay>
  </window>
  <window>
    <BookName>Growth</BookName>
    <StartDate>2/1/2022</StartDate>
    <EndDate>2/2/2022</EndDate>
    <NoOfDays>2</NoOfDays>
    <ReasonsByDay>Soldout,Soldout</ReasonsByDay>
  </window>
  <window>
    <BookName>Growth</BookName>
    <StartDate>2/5/2022</StartDate>
    <EndDate>2/6/2022</EndDate>
    <NoOfDays>2</NoOfDays>
    <ReasonsByDay>No Stock,No Stock</ReasonsByDay>
  </window>
  <window>
    <BookName>Growth</BookName>
    <StartDate>2/12/2022</StartDate>
    <EndDate>2/15/2022</EndDate>
    <NoOfDays>4</NoOfDays>
    <ReasonsByDay>Soldout,Soldout,Soldout,Soldout</ReasonsByDay>
  </window>
  <window>
    <BookName>Roundup</BookName>
    <StartDate>2/2/2022</StartDate>
    <EndDate>2/5/2022</EndDate>
    <NoOfDays>4</NoOfDays>
    <ReasonsByDay>Damage,Damage,Damage,Damage</ReasonsByDay>
  </window>
  <window>
    <BookName>Roundup</BookName>
    <StartDate>2/14/2022</StartDate>
    <EndDate>2/15/2022</EndDate>
    <NoOfDays>2</NoOfDays>
    <ReasonsByDay>No Stock,No Stock</ReasonsByDay>
  </window>
  <window>
    <BookName>State of Union</BookName>
    <StartDate>2/5/2022</StartDate>
    <EndDate>2/12/2022</EndDate>
    <NoOfDays>8</NoOfDays>
    <ReasonsByDay>Soldout,Soldout,Soldout,Soldout,No Stock,Soldout,No Stock,No Stock</ReasonsByDay>
  </window>
</table>

[rfarmakis11]

Spoiler

[harrame]

Spoiler