Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Link several date fields from one table into Master Calendar

I have a Master Calendar loaded table.

I have a Job Table that includes:

JobCreatedate.

JobDueDate

JobRequiredbyDate

JobCompletedDate

JobClosedDate

Is there any way to link these two together so that when a Date is selected on the master calendar, it shows what jobs where created, due, reuiredby completed and closed on that day? Kind of Like an overview of what happened.

I understand that I could resident load the job table and relink it to the calander for each date field, but that seems a bit cumbersum if I want to show job details for each section, especially when I have several other tables linked into the job table.

9 Replies
aadilmadarveet
Valued Contributor

Link several date fields from one table into Master Calendar

i suggest you have one date field in the calendar table which will have all the dates your Master calendar table field has so that it can be linked and any selection made would take effect...

hope that helps..

Not applicable

Re: Link several date fields from one table into Master Calendar

I understand that it would effect the selection in the table.

Right now if I link one of the date fields in, like example createdate,  if I select a date it will show all jobs created that day, and show only when those specific jobs are due, closed etc.

I was wondering if I could make it show when a date is selected all dates that happened on that day. I am assuming now that this can not be done without linking the other fields in a separate load statement.

Example:

Job, created, closed, due

1, 5/7/2011, 5/15/2011, 5/10/2011

2, 5/15/2011, 6/01/2011, 6/02/2011

If the calendar date 5/15/2011 is selected I want to see job 1 since it was closed then, and also see job 2 that was created then.

Right now if I link create date only I would only see job 2.

aadilmadarveet
Valued Contributor

Re: Link several date fields from one table into Master Calendar

Yes you are correct... on that sense...

I suppose the date field in the other table have all the dates...  5/10/2011, 5/7/2011, 5/15/2011, 6/01/2011 and 6/02/2011

So... all you need is to have a field in the Calendar table which has all the dates... and the other fields like Created, closed, due will have dates or flags for their particular dates... in this way... you can see created and closed as well..

Hope this helps...

thanks,

Aadil

Not applicable

Re: Link several date fields from one table into Master Calendar

I am not sure I understand.

Would you mean like in the calendar table create fields:

Completed, created, closed, due

then link those fields into the job table basically creating 4 links between the two tables?

Not applicable

Re: Link several date fields from one table into Master Calendar

Got the exact same problem, was there ever any sollution found to this?

Not applicable

Re: Link several date fields from one table into Master Calendar

Hi marcsliving,

I have a solution regarding this. try this->

*******************************************

JobTable:

LOAD *

from JobTable;

CONCATENATE

LOAD *,

          JobCreatedate AS DATE,

          Job AS JobCreated

RESIDENT JobTable;

CONCATENATE

LOAD *,

          JobDuedate AS DATE,

          Job AS JobDue

RESIDENT JobTable;

CONCATENATE

LOAD *,

          JobRequiredbyDate AS DATE,

         Job AS JobRequired

RESIDENT JobTable;

CONCATENATE

LOAD *,

          JobCompletedDate AS DATE,

         Job AS JobCompleted

RESIDENT JobTable;

Calendar:

LOAD DATE

from Calendar;

*************************************************************

when you select DATE from Calendar you will get count(JobCompleted),count(JobDue),count(JobRequired),count(JobCreated) etc regarding this DATE.

Regards

Apurva

Not applicable

Re: Link several date fields from one table into Master Calendar

Try the folloing script replace date with date fields in various table and table 1 to table 3 with the name of the table.

  /*Master Callender prepared based on all dates avaliable*/

 

LET varMinDate = Num(rangemin(Peek(‘Date’, 0,'Table1'),Peek(‘Date’, 0,' Table2'),Peek(‘Date’, 0,' Table3')));

 

LET varMaxDate = Num(rangemax(Peek(‘Date’, -1,' Table1),Peek(‘Date’, -1,' Table2'),Peek(‘Date’, -1,' Table3')));

 

LET vToday = num(today());

//************************Temporary Calendar***************************

//Create all days in the range from varMinDate to varMaxDate

TempCalendar:

 

LOAD

$(varMinDate)+Iterno()-1 AS Num,

 

Date($(varMinDate)+Iterno()-1) AS TempDate

 

AUTOGENERATE 1 WHILE $(varMinDate)+Iterno()-1<= num(today());

//**************************Master Calendar******************************

//Building the master calendar with most date dimensions

MasterCalendar:

load

 

TempDate as DAILY_Date,

week(TempDate) as Week,

Year(TempDate) as Year,

Month(TempDate) as Month,

Hour (TempDate) as Hour,

Day(TempDate) as Day,

Year2date(TempDate)*-1 as CurYTDFlag,

Year2date(TempDate,-1)*-1 as LastYTDFlag,

Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

Week(TempDate)& '-'&Year(TempDate) as WeekYear,

date(Today()-1) as Yesterday,

Week (today()) as CurrentWeek,

(Week (today()))-1 as LastWeek,

Weekday(TempDate) as WeekDay

resident TempCalendar

order by TempDate Asc;

Drop Table TempCalendar;

jim_chan
Contributor III

Re: Link several date fields from one table into Master Calendar

only 1 question - where did the "TempDate as DAILY_Date," daily_date come from?

jim_chan
Contributor III

Re: Link several date fields from one table into Master Calendar

if is based on my script , my master calendar, start from

MinMax:

load

Min(Date(main_date)) as MinDate,

Max(Date(main_date)) as MaxDate

Resident LINK_TABLE;

LET vMinDate = Num(Peek('MinDate', 0 , 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0 , 'MinMax'));

LET vToday = $(vMaxDate);

Community Browser