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.

[Colin-Albert]

This post on creating missing data should give the answer you need.

Generating Missing Data In QlikView

[#3]

Hi Collin,

Thank you very muck for your response.

I'll try to see it !

[#4]

I create a IF statement : IF(ISNULL(END_DATE),DATE(TODAY()) , PREVIOUS(END_DATE)) AS DATE_TO

but the reload takes more times : 58 min!!

for a store_id XXXXX with step_id 2 to 26/07/2013 and step_id 4 29/09/2013

I have for step_id 2 from 26/07/2013 to Today() and for step_id 4 from 29/09/2013 to today()

I just want for the store_id XXXXX step_id 2 starts from 26/07/2013 to 28/09/2013 and for step_id 4 starts from 29/09/2013 to Today() because the end_date is null().

How can I realize it ?

Thanks for your help.

TMPA:
LOAD
STORE_ID,
STEP_ID,
TRAINING_DATE,
END_DATE
FROM TABLE.QVD ;

LOAD

STORE_ID,
STEP_ID,
DATE(DATE_STEP+ITERNO()) AS DATE_STEP,
TRAINING_DATE,
END_DATE
WHILE DATE_STEP+ITERNO() < DATE_TO
;
LOAD
STORE_ID,
STEP_ID,
TRAINING_DATE,
END_DATE,
IF(ISNULL(END_DATE),DATE(TODAY()) , PREVIOUS(END_DATE)) AS DATE_TO
RESIDENT TMPA
WHERE STORE_ID = PREVIOUS(STORE_ID)
ORDER BY STORE_ID, DATE_STEP DESC;

CALENDAR:
LOAD
*,
DAY(DATE_STEP) AS CALENDAR_DAY,
WEEKDAY(DATE_STEP) AS CALENDAR_WEEKDAY,
WEEK(DATE_STEP) AS CALENDAR_WEEK,
DUAL('S'&WEEK(DATE_STEP), WEEK(DATE_STEP)) AS CALENDAR_WEEK2,
WEEKNAME(DATE_STEP) AS CALENDAR_WEEKNAME,
MONTH(DATE_STEP) AS CALENDAR_MONTH,
NUM(MONTH(DATE_STEP)) AS CALENDAR_MONTH_NUM,
MONTHNAME(DATE_STEP) AS CALENDAR_MONTHNAME,
DUAL('QTD-'&CEIL(MONTH(DATE_STEP)/3),CEIL(MONTH(DATE_STEP)/3)) AS CALENDAR_QUARTER,
DUAL('HALF-'&CEIL(MONTH(DATE_STEP)/6),CEIL(MONTH(DATE_STEP)/6)) AS CALENDAR_SEMESTER,
QUARTERNAME(DATE_STEP) AS CALENDAR_QUARTERNAME,
YEAR(DATE_STEP) AS CALENDAR_YEAR,
WEEKYEAR(DATE_STEP) AS CALENDAR_WEEKYEAR
;
LOAD DATE(MINDATE+ITERNO()-1) AS DATE_STEP

WHILE MINDATE+ITERNO()-1 < MAXDATE;
LOAD
MIN(DATE_STEP) AS MINDATE,
MAX(DATE_STEP) AS MAXDATE
RESIDENT TMPA;


EXIT SCRIPT;

[#5]

Take a look how the missing data is being generated based on an outer join.  and showed in the chart.

[#6]

Thank you Mario,

I'm going to see that and look how can I change to correspond to my needs.

[#7]

Marco, the date was not propagate like I want.

I found the date for the STEP_ID 4 and data are not propogate.

For Exemple : The STORE_ID 261236121 have a STEP_ID 2 to 31/05/2013 and a TRAINING_DATE to 5/09/2013 for STEP_ID 4 AND NOT date for STEP_ID 7 (END DATE is NULL).

So, the data would be :

STORE_ID                           STEP_ID                       DATE_STEP

261236121                                  2                                31/05/2013       

261236121                                  2                                01/06/2013

261236121                                  2                                02/06/2013

261236121                                  2                                24/09/2013

......................................................................................................... etc

261236121                                  4                                   25/09/2013

261236121                                  4                                   26/09/2013

261236121                                  4                                   27/09/2013

......................................................................................................... etc

261236121                                  4                                   06/01/2015

(because the END_DATE is NULL and STEP_ID 7 don't exist at this day).

[#8]

Can

[#9]

Can you send me some sample data and the expected output I will try to give you a hand on this. sometimes is better for us to help each other based on a document and expected output.

[#10]

See attachment.