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

Prajju

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

Unknown

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.

Unknown

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.