Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Check this.
http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date
Regards
ASHFAQ
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
Hi Anand,
I am getting the following error
.. field not found of Vmindadte
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
Hi Anand,
Date is coming correctly in mm/dd/yyyy. if i change that date formatalso i am getting the same error
Yes because you have to change the date format also if possible provide the sample data.
Regards
Anand
Hi,
Try to change format field in the excel
Hi,
If you got correct answer from the thread then close the thread by appropriate answer.
Regards
Anand