topic Re: How can I create a specific calendarDate ? in QlikView

How can I create a specific calendarDate ?

Mon, 05 Jan 2015 22:03:30 GMT

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.

Re: How can I create a specific calendarDate ?

Colin-Albert — Mon, 05 Jan 2015 22:18:43 GMT

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

Generating Missing Data In QlikView

Re: How can I create a specific calendarDate ?

Mon, 05 Jan 2015 23:19:20 GMT

Hi Collin,

Thank you very muck for your response.

I'll try to see it !

Re: How can I create a specific calendarDate ?

Tue, 06 Jan 2015 04:49:25 GMT

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;

Re: How can I create a specific calendarDate ?

Tue, 06 Jan 2015 04:55:47 GMT

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

Re: How can I create a specific calendarDate ?

Tue, 06 Jan 2015 05:56:45 GMT

Thank you Mario,

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

Re: How can I create a specific calendarDate ?

Tue, 06 Jan 2015 06:31:07 GMT

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).

Re: How can I create a specific calendarDate ?

Tue, 06 Jan 2015 07:03:38 GMT

Can

Re: How can I create a specific calendarDate ?

Tue, 06 Jan 2015 07:04:28 GMT

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.

Re: How can I create a specific calendarDate ?

Tue, 06 Jan 2015 07:05:48 GMT

See attachment.

Re: How can I create a specific calendarDate ?

Wed, 07 Jan 2015 03:09:52 GMT

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;

Re: How can I create a specific calendarDate ?

Wed, 07 Jan 2015 03:29:36 GMT

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;

Re: How can I create a specific calendarDate ?

Thu, 08 Jan 2015 06:02:27 GMT

Hi Mario,

Thank you very much

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

Many thanks.