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

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 

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.