Creating Calendar

[#1]

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

[ashfaq_haseeb]

Hi,

Check this.

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

Regards

ASHFAQ

[its_anandrjs]

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

[#4]

Hi Anand,

I am getting the following error

.. field not found of Vmindadte

[its_anandrjs]

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

[#6]

Hi Anand,

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

[its_anandrjs]

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

Regards

Anand

[ecolomer]

Hi,

Try to change format field in the excel

[its_anandrjs]

Hi,

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

Regards

Anand