Adding a new date column which is based on another Date column

Hi,

 

I am trying to build a card that shows the items and the date it goes out of stock and the date it comes back on stock. I have the following dataset:

 

Item

Country

Date

In_stock

In_stock_previous_week

A

USA

Sep 17

0

1

A

USA

Sep 24

0

0

A

USA

Oct 1

0

0

A

USA

Oct 8

1

0

A

USA

Oct 15

0

1

A

USA

Oct 22

0

0

A

USA

Oct 29

0

0

 

Ideally I would want a dataset like this:

Item

Country

Date

In_stock

In_stock_previous_week

Date Out Of Stock

Date Back In Stock

A

USA

Sep 17

0

1

Sept 17

Oct 8

A

USA

Sep 24

0

0

Sep 17

Oct 8

A

USA

Oct 1

0

0

Sep 17

Oct 8

A

USA

Oct 8

1

0

 

 

A

USA

Oct 15

0

1

Oct 15

-

A

USA

Oct 22

0

0

Oct 22

-

A

USA

Oct 29

0

0

Oct 29

-

 

The date_Out_Of_stock is the Date when in_stock = 0 and In_stock_previous_week = 1.

The date_back_in_stock is the next Date when in_stock = 1 and In_stock_previous_week = 1.

 

Is this possible to achieve in a Beast Mode or SQL Dataflow and how?

 

Thanks,

Prajwal

Best Answer

  • Unknown
    Accepted Answer

    Hi, Prajju,

    I'm going to throw down some data modeling concepts that will help explain how I think about problems like this. That will take some space on the page, so if you already know it or simply don't care feel free to skip to the solution Smiley Very Happy

     

    Data modeling:
    In your source dataset, you have a weekly running log of when items are in stock. And you want to add the "Date Out Of Stock" and "Date Back In Stock" fields to that dataset. Let's take a minute and think about the structure of the data.

     

    First, your source dataset has three key identifying columns: Item, Country, and Date. Think of each Item|Country|Date as a distinct object, with every other column in the table describing some attribute of the object.

     

    Now, consider the new columns you want to add: "Date Out Of Stock" and "Date Back In Stock". Those columns don't fit the data model of the source data because the date an item is expected to come back in stock has nothing to do with what week of the year we're currently in. Your new columns are attributes of slightly different objects: they're attributes of "Item|Country" objects.

     

    "In_Stock" and "In_Stock_Previous_Week" are attributes of "Item|Country|Date" objects, so it makes sense for them to be included in the "Item|Country|Date" table. However, "Date Out Of Stock" and "Date Back In Stock" are attributes of "Item|Country" objects, so those fields don't really fit in the "Item|Country|Date" table. Instead, think of them as being a separate dataset. Do whatever processing is necessary on that separate dataset, and then join it back onto the source data at the end. It seems like extra work, but especially when the data or analysis becomes more complex, I find it is easier when the data is organized in a good model.

     

    Solution:
    First, for each Item|Country, you'll need a list of the dates the product came into or ran out of stock. Then, you'll want to give each stock-in or stock-out event a row number. These steps could be done in SQL or in ETL, and it would be easier in ETL. The result would be something like this:

    Item | Country | Event        | Date  | EventOrder
    A USA Out of stock 9/17 1
    A USA In stock 10/8 2
    A USA Out of stock 10/15 3

    In SQL, you can accomplish that Event Order result by declaring a row number variable and then incrementing the row number up one as appropriate. You'll want to partition by Item and Country. Check out this article in the Knowledge Base: http://knowledge.domo.com?cid=rank. In ETL, you can use a "Rank & Window" block.

     

    Next, you'll want to create another table that will show the periods of time during which a product was in stock and out of stock. The output should be something like this:

    Item | Country | Status       | StartDate | EndDate
    A USA Out of stock 9/17 10/8
    A USA In stock 10/8 10/15
    A USA Out of stock 10/15

    You can accomplish that by joining the Event Order table to itself. In SQL, join on Item=Item, Country=Country, and EventOrder=EventOrder+1. This could also be done in ETL, but you would need an intermediate step to create a second version of the Event Order table where EventOrder = EventOrder - 1. Then you would join the two versions of the table together.

     

    The last step is to join the status periods table to your source data. Again, you'll want to join on Item=Item and Country=Country. You'll also need to include the dates in the join condition. The objective is to join only one status period record to each row in the source data. Do that by including join conditions such that the join is performed when the Date from the source table is between the StartDate and EndDate of the status periods table. Be sure to account for that NULL End Date for the last status period.

     

     

    Also, please feel free to reach out to your Domo account executive or CSM - they can with you to line up Domo resources to help if needed.

     

    Hope this information is helpful. Cheers.