Compare date in column algorithm

William Le

Dear All,

 

I have 2 cube, the first cube is a date cube with one dimension is article, the second cube is single cube with 2 dimensions are Month and article. I want to make a new single cube with 2 dimensions are Month and article. i want to fill the value in new cube by second cube with the logic is if the month in second cube equal or greater than date in first cube for the same article, the value in new cube will equal to the value in first cube. Is it possible to do by column algorithm. And in the algorithm, do all the blocks need to the same dimension? Thank you very much.

Samuele Marchetto

Hi, you need 2 service cubes: a cube (single), by Article, where you write the month of the date of first cube as single (that means, date 01/01/2018 must be converted in 201.801 via ETL or Nexel), a cube (single), by Month and Article, where you write the value of Month entity for every article (view procstec1 attached)

 

Then you can calculate via dataflow your algorithm (view procstep2 attached)

 

 

In my example I obtain this:

 

 

Bye

William Le

Hi Samuele Marchetto,

 

Thank you very much.

William Le

Hi Samuele Marchetto,

 

In case, i have around 10,000 article and the date range from 2014-2025 and maybe it will have one or two entity as dimension example manufacture, customer. Then it will take a lot of time to fill the service cube and some time it doesnt work correctly. I created a service cube with Month and Article entity as dimensions and the i created a data flow at below picture, after run procedure, nothing happen, the service cube is not filled in. Do you have any suggestions? Thank you very much.

 

Samuele Marchetto

Hi, if the dataflow is slow or it doesn't work, you can export your original cube and reload it to service cubo using an ascii datareader, where you load Month field into the infocube value. You have only to pay attention that for each article (or other dimension) you load month value only one time (or it sum the month number much times).

Anastasia Pesyakova

@Matthew Sobon hi Matt, please see the details above regarding the dates comparison if you want to store the result in a cube.