How can I flag all periods between 2 dates?

The Hackett Group · 2024-10-02T08:18:49+00:00

There was an error rendering this rich post.

Hi,

I need to load some discount values from a table in SQL but the table does not have a month column. Instead, we have 2 separate columns; for Start Date and End Date.

In normal circumstances, I would just take the last value from each of these dates and subtract the two lists, leaving me with the period between being flagged, but this is not possible in this dataset, as we will have multiple start and end dates for a given product.

Below is an example of the result that I need to achieve:

Has anyone dealt with a similar problem before? I have a workaround that I can perform in SQL, but I'm sure there must be a better solution.

Thanks!

Find more posts tagged with

Dataflow

Time range

Data Loading Strategies

Data Reader

SQL Server

Accepted answers

Federica Antonelli

Hello @The Hackett Group - another approach is the following:

Load the start and the end date into two cubes (cube type: date) - you need to add all the dimensions in the cube to be able to add all the start and end dates for each product if you have multiple dates for each. (f.e if you have numerous promo for each product you need the entity promotion);
Use the dataflow time offset (absolute) - to shift Cube the values (dates) on the time dimension;

In this way you will have the first two columns of the table you presented above.

To get the result in column three, create the following procedure:
- Dataflow 1:
  - Temp cube (C) = Cube in column 1 - Cube in column 2 (with Offset -1) applied → This is because you need to include in your range also the end date.
- Dataflow 2
  - target cube = if(a>=0,a,0) - where a is the temp cube calculated in the dataflow 1 with the last value applied. → this is writing 1 from the start to the end date, included.

Hope this helps

Federica

All comments

Helmut Heimann

Hi @The Hackett Group ,

first of all, you'll need to calculate the number of days between start- and end-date to be used as time offset. Then you set the start date in the output cube to 1 and build a dataflow with 3 blocks. a and c will be your output cube, b will be the time offset cube. The algorithm for c=if(a=1,a*2,0) .

Set the dynamic offset in the flow's configuration to relative forward using the time offset as driver (that's why this cube has to be part of the flow's layout).

That will flow a "2" to the end date.

The next step in your procedure is a dataflow with twice the output cube (a and b), a set to "Last Value". The algorithm b=a.

Then a dataflow again with twice the output cube b=if(a=1,1,0) to get rid of the "2".

That should do the trick.

If you know the end-date you could set it directly to "2" in the output cube and don't need to consider the time-offset flow. Skip directly to b=LastValue(a).

Best,
Helmut

Federica Antonelli

Hello @The Hackett Group - another approach is the following:

Load the start and the end date into two cubes (cube type: date) - you need to add all the dimensions in the cube to be able to add all the start and end dates for each product if you have multiple dates for each. (f.e if you have numerous promo for each product you need the entity promotion);
Use the dataflow time offset (absolute) - to shift Cube the values (dates) on the time dimension;

In this way you will have the first two columns of the table you presented above.

To get the result in column three, create the following procedure:
- Dataflow 1:
  - Temp cube (C) = Cube in column 1 - Cube in column 2 (with Offset -1) applied → This is because you need to include in your range also the end date.
- Dataflow 2
  - target cube = if(a>=0,a,0) - where a is the temp cube calculated in the dataflow 1 with the last value applied. → this is writing 1 from the start to the end date, included.

Hope this helps

Federica

The Hackett Group

https://community.board.com/discussion/comment/18646#Comment_18646

Thanks all, I ended up using a modified version of this - I didn't need the date cubes because I had flags already populated.

A very quick and tidy solution which has helped me replace a slow and cumbersome SQL process! :)