Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
This post on creating missing data should give the answer you need.
Hi Collin,
Thank you very muck for your response.
I'll try to see it !
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;
Take a look how the missing data is being generated based on an outer join. and showed in the chart.
Thank you Mario,
I'm going to see that and look how can I change to correspond to my needs.
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).
Can
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.
See attachment.