How can I create a specific calendarDate ?

[#1]

Hi all,

I have a sample data , I need to create days from DATE_STEP to today() if isnull END_DATE.

STORE_ID                   DATE_STEP               TRAINING_DATE                END_DATE                 STEP_ID

XXXXXXA                     01/01/2013                      -                                            -                                    2

XXXXXXA                     27/08/2013                       -                                           -                                    4

XXXXXXB                     13/08/2013                       -                                           -                                    2

XXXXXXC                     05/02/2013                       -                                           -                                    2

XXXXXXC                     10/06/2013                       -                                           -                                    7

I need to  create days :

STORE_ID                   DATE_STEP               STEP_ID

XXXXXXA                     01/01/2013                     2

XXXXXXA                     02/01/2013                     2

.....                                 .....                                  2

XXXXXXA                     26/08/2013                     2

XXXXXXA                     27/08/2013                     4

...                                   ....                                   4

XXXXXXA                     05/01/2015                     4

XXXXXXB                     13/08/2013                      2

....

XXXXXXB                     05/01/2015                       2

XXXXXXC                     05/02/2013                       2

XXXXXXC                     06/02/2013                       2

....                                   ....                                    2

XXXXXXC                     09/06/2013                       2

XXXXXXC                     10/06/2013                       7

How can I achive that ?

Thank for your help.

[#2]

I think it is working with

//tabStoreStepsfinal:

LOAD STORE_ID,

    Date(DATE_STEP+IterNo()-1) as DATE_STEP,

    STEP_ID

While DATE_STEP+IterNo()<= PrevDate;

LOAD STORE_ID,

     DATE_STEP,

     if (isNull(Previous(DATE_STEP))=0 AND STORE_ID <> Previous(STORE_ID),Date(Today(0)),

          if (isNull(Previous(STORE_ID)),Date(Today(0)),

     Previous(DATE_STEP)

     ))  as PrevDate,

     STEP_ID

Resident tabStoreSteps

Order By STORE_ID, STEP_ID DESC;

[#3 · Accepted solution]

And in the case You want STEP7 to be the top date when it exists , then use the following code,

LOAD STORE_ID,

    Date(DATE_STEP+IterNo()-1) as DATE_STEP,

    STEP_ID

While DATE_STEP+IterNo()<= PrevDate;

LOAD STORE_ID,

     DATE_STEP,

     if (isNull(Previous(DATE_STEP))=0 AND STORE_ID <> Previous(STORE_ID) AND (STEP_ID<>7),Date(Today(0)),

          if (isNull(Previous(STORE_ID)),Date(Today(0)),

                 Previous(DATE_STEP)

     ))  as PrevDate,

     STEP_ID

Resident tabStoreSteps

Order By STORE_ID, STEP_ID DESC;

[#4]

Hi Mario,

Thank you very much

It is wonderful. It's exactly what I need!

Many thanks.