topic Re: Creating Calendar in QlikView

Creating Calendar

Sun, 02 Nov 2014 12:30:38 GMT

Hi Experts,

I have data from 3 excels in which i have common date field as Exception date from three excels. now i wanted to derive

calendar.data here in 3 excels is almost same but number of fields is different. i have almost 40 fields in every excel

Any help would be appreciated

Re: Creating Calendar

ashfaq_haseeb — Sun, 02 Nov 2014 12:33:07 GMT

Hi,

Check this.

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

Regards

ASHFAQ

Re: Creating Calendar

its_anandrjs — Sun, 02 Nov 2014 12:41:42 GMT

Hi,

Load all this excel files and as you say Exception date load three excel file and from Exception date connect the master calendar see the load script

T1:

Load

[Exception date]

From Excel1

concatenate(T1)

Load

[Exception date]

From Excel1

concatenate(T1)

Load

[Exception date]

From Excel1

//Master Calendar

MinMax:

Load

Num(Min([Exception date])) as [Min Exception date],

Num(Max([Exception date])) as [Max Exception date]

Resident T1;

Let vMinDate = NUM(Peek('Min Exception date',0,'MinMax'));

Let vMaxDate = NUM(Peek('Max Exception date',0,'MinMax'));

MasterCalendar:

Load

$(vMinDate ) + RowNo() - 1 as TempDate,

Date($(vMinDate ) + RowNo() - 1) as [Exception date]

AutoGenerate 1;

While $(vMinDate ) + RowNo() <= $(vMaxDate);

Regards

Anand

Re: Re: Creating Calendar

Sun, 02 Nov 2014 13:04:42 GMT

Hi Anand,

I am getting the following error

.. field not found of Vmindadte

Re: Re: Creating Calendar

its_anandrjs — Sun, 02 Nov 2014 13:50:54 GMT

Hi,

Try this example in place of the inline table use your table and use master calendar code as it is, and may be there is some date format issue in your tables if persists create date format for this fields and use master calendar.

Source:

LOAD Date(Date#([Exception date],'DD/MM/YYYY')) as [Exception date];

Load * Inline

[ Exception date

01/01/2012

25/02/2013

14/01/2013

01/04/2014 ];

Concatenate(Source)

LOAD Date(Date#([Exception date],'DD/MM/YYYY')) as [Exception date];

Load * Inline

[ Exception date

01/01/2014

23/02/2014

16/04/2014

06/07/2014 ];

//Master Calendar Code

MinMax:

Load

Num(Min([Exception date])) as [Min Exception date],

Num(Max([Exception date])) as [Max Exception date]

Resident Source;

Let vMinDate = NUM(Peek('Min Exception date',0,'MinMax'));

Let vMaxDate = NUM(Peek('Max Exception date',0,'MinMax'));

Let vDays = vMaxDate - vMinDate + 1 ;

MasterCalendar:

Load

//RowNo() as rid,

$(vMinDate) + RowNo() - 1 as TempDate,

Date($(vMinDate) + RowNo() - 1) as [Exception date]

AutoGenerate 1

While ( $(vMinDate) + RowNo() - 1) < Num($(vMaxDate));

Regards

Anand

Re: Creating Calendar

Sun, 02 Nov 2014 15:46:43 GMT

Hi Anand,

Date is coming correctly in mm/dd/yyyy. if i change that date formatalso i am getting the same error

Re: Creating Calendar

its_anandrjs — Sun, 02 Nov 2014 16:31:42 GMT

Yes because you have to change the date format also if possible provide the sample data.

Regards

Anand

Re: Creating Calendar

ecolomer — Sun, 02 Nov 2014 16:32:38 GMT

Hi,

Try to change format field in the excel

Re: Creating Calendar

its_anandrjs — Wed, 12 Nov 2014 16:52:04 GMT

Hi,

If you got correct answer from the thread then close the thread by appropriate answer.

Regards

Anand