topic Dealing with two dates in QlikView

Dealing with two dates

anuradhaa — Tue, 15 Jul 2014 09:28:44 GMT

Hi,

I have two tables and in those two tables i use two different dates.

So i want to use common date in date filters.

say my tow dates are

Mydate in mytable and machinedate in machine table.

I want to use common date in my calander

Thanks

Re: Dealing with two dates

amit_saini — Tue, 15 Jul 2014 09:32:46 GMT

Anuradha,

Follow this:

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

Thanks,
AS

Re: Dealing with two dates

Tue, 15 Jul 2014 09:38:41 GMT

Hi Anuradha

Try to use a MasterCalendar like this:

LET varMinDate = StartDate; eg:'6/15/2001'

LET varMaxDate = EndDate; eg: Today();

//************TempCalendar***********
TempCalendar:
LOAD
$(varMinDate) + rowno() - 1 AS DateNumber,
date ($(varMinDate) + rowno() - 1) AS TempDate
AUTOGENERATE
$(varMaxDate) - $(varMinDate) + 1;

//*************Master Calendar************
MasterCalendar:
LOAD
TempDate AS Date,
Week (TempDate) AS Week,
Year (TempDate) AS Year,
Month (TempDate) AS Month,
Day (TempDate) AS Day,
Weekday (TempDate) AS WeekDay,
'Q' & Ceil(Month (TempDate)/3) AS Quarter,
Date( Monthstart (TempDate), 'MMM-YYYY') AS MonthYear,
Week (TempDate) & '-' & Year (TempDate) AS WeekYear

Resident TempCalendar
Order by TempDate ASC;

Drop Table TempCalendar;

However if you want 2 different dates for analysis, you need 1 calendar per date...

Hope this is helpful for you!

BRGDS,

Anthony

Re: Dealing with two dates

sudeepkm — Tue, 15 Jul 2014 09:52:10 GMT

It seems you want to associate both the tables based on the date fields.

May be you can try the code below.

// create a table by loading distinct dates from both the tables and join

Tab1:

load OtherFields1,[Date Field1] as [Date Field] from tab1;

Tab2:

load OtherFields2,[Date Field2] as [Date Field] from tab2;

TempCalendar:

load Distinct [Date Field] Resident Tab1;

Join LOAD Distinct [Date Field] Resident Tab2;

MainCalendar:

load [Date Field] Resident TempCalendar;

DROP Table TempCalendar;