How to link a date field when there is no common field

[#1]

Hi Everyone,

I am struggling here.  I need to be able to filter on items from my location table and then from dates, all other fields will be used in the expressions within individual objects.  I am struggling to find a way to connect my TableNameMonthYear fields to the datelink table.  See attached image for the current table view.

[Anonymous]

What I understand is you need to introduce new fields in DateLink table Just

load

Date,

Month(Date)&Year(Date) as DateMonthYear,

DateMonthYear as Table1NameMonthYear ,

.

.

.

Month(Date&Year(Date) as TableNNameMonthYear ,

for all the table TableNameMonthYear fields

....

then you will get link with all the tables

[simenkg]

This will create logical loops and make the application useless.

Check out the following blog and see if that is what you are looking for.

Canonical Date

[sunny_talwar]

You might find this blog to be useful:

Canonical Date

and also find attached an application I found on the community which will also be useful.

Best,

S

[avinashelite]

create a master calender and link the master  calender with data field , this should solve your issue

[#6]

I actually used that qvw to create the sub calendars off of the data tables themselves but the issue I ran into is that the tables do not have a ID such as Order ID that is shared, some link to the location table off of Segment and some link off of OU so if I introduce a unique ID it creates loops in the model which then render it useless.  I will give the Canonical Date blog a read through and see if it offers some insight.

[simenkg · Accepted solution]

A solution to this is to concatenate all the different "Segment" tables and create a Master Calendar.

I don't know if Segment is some identifier of the table. If so you can just concatenate them all:

Fact:
Load *, SafetyMonthYear as %DateKey from Safety....:;

Concatenate(Fact)
Load *, HRMonthYear as %DateKey from HRData....;

.

.

.

.

.

.

.

MasterCalendar:
Load *, Month&'Year as %DateKey from CommonCalendar ....;

If Segment does not identify the table then add a FactType-field to your tables.

Fact:
Load *, SafetyMonthYear as %DateKey, 'Safety' as FactType from Safety....:;

Concatenate(Fact)
Load *, HRMonthYear as %DateKey, 'HR' as FactType from HRData....;