topic Re: Link Table & Master Calendar Help in QlikView

Link Table & Master Calendar Help

Anonymous — Wed, 10 Dec 2014 04:47:02 GMT

Hi All,

I am having following table structure. For this I need to create link table and I need to have a master calendar.

So kindly help me on this

Dom:

DOM_ID,

CREATION_TIME

UPDATION_TIME

Services:

DOM_ID,

Service_Unique_id

CREATION_TIME

UPDATION_TIME

Version:

Service_Unique_id,

SLD_Unique_Id,

CREATION_TIME

UPDATION_TIME

SLA:

SLD_Unique_Id,

SLA_UId,

CREATION_TIME

UPDATION_TIME

App_Ver:

SLA_UId,

AV_ID,

CREATION_TIME

UPDATION_TIME

Buss:

AV_ID,

CREATION_TIME

UPDATION_TIME

SLD:

SLD_Unique_Id,

E_ID

CREATION_TIME

UPDATION_TIME

END:

E_ID

CREATION_TIME

UPDATION_TIME

Re: Link Table & Master Calendar Help

amit_saini — Wed, 10 Dec 2014 04:56:27 GMT

Hi,

You can use like below:

Here the Datefield should be you Date field in your tables.

LET vMinDate = num(makedate(2012));

LET vMaxDate = num(today());

Datefield:

LOAD

$(vMinDate) + IterNo() -1 as Datefield

AUTOGENERATE (1)

WHILE $(vMinDate) + IterNo() -1 <= $(vMaxDate);

Calendar:

LOAD

Datefield as Date_Key%,

date(Datefield) as Date,

year(Datefield) as Year,

month(Datefield) as Month,

day(Datefield) as Day,

week(Datefield) as Week,

weekday(Datefield) as Weekday

RESIDENT Datefield;

drop table Datefield;

Thanks,

Re: Link Table & Master Calendar Help

SunilChauhan — Wed, 10 Dec 2014 04:58:51 GMT

attached sample if possible

Re: Link Table & Master Calendar Help

Anonymous — Wed, 10 Dec 2014 05:02:31 GMT

Hi Amit,

I dont have any issues in creating Master Calendar. All I need to create a link table should be make a relationship across all the tables.

Re: Link Table & Master Calendar Help

jonathandienst — Wed, 10 Dec 2014 05:09:40 GMT

Based ion your field names (one new field added per table), it looks to me like you should be joining these tables rather than loading them as separate tables and linking them. Are you sure that what you are asking for is the most suitable design for your model?

Jonathan

Re: Link Table & Master Calendar Help

Anonymous — Wed, 10 Dec 2014 05:56:26 GMT

Hi Jonathan,

I got you. My scenario is like I should have link table in the data model. Also I need to have a master calendar by including all the date fields.

Here the key SLD_Unique_Id is common cross 3 tables. When I am creating link table by using SLD_Unique_Id

other table's (Domain,Services,App_Ver,Buss,END) date fields are not linked properly.

Here I have attached my sample application