common date

[#1]

Newbie needing help here.. been trying to solve this for days.. using the canonical dates tutorial..

mine is different though as all of the dates should show up the same

I have 4 dates

Fin_Month

Cust_Month

Snapshot_Month

People_Month

tried to create a tempcal and master calendar.. but its isolated from the rest of my data

i would like to just select Snapshot Month and it should automatically select the same months as well

RepMonth	Snapshot Month	Cust_Month	Fin_Month
31-Dec-14	12/31/2014	12/31/2014	12/31/2014
31-Jan-15	01/31/2015	01/31/2015	01/31/2015
28-Feb-15	02/28/2015	02/28/2015	02/28/2015
31-Mar-15	03/31/2015	03/31/2015	03/31/2015
30-Apr-15	04/30/2015	04/30/2015	04/30/2015
31-May-15	05/31/2015	05/31/2015	05/31/2015
30-Jun-15	06/30/2015	06/30/2015	06/30/2015
31-Jul-15	07/31/2015	07/31/2015	07/31/2015
31-Aug-15	08/31/2015	08/31/2015	08/31/2015
30-Sep-15	09/30/2015	09/30/2015	09/30/2015
31-Oct-15	10/31/2015	10/31/2015	10/31/2015
30-Nov-15	11/30/2015	11/30/2015	11/30/2015
31-Dec-15	12/31/2015	12/31/2015	12/31/2015

[jpenuliar]

Third Table is not linked to the Bridge,

Another trick is  a "Date Island" which you can use,

But I prefer the rwunderlich technique.

[#3]

hi rob.. sorry.. this is so similar to reading a map (girls find it hard to read maps).. so we just ask .. ive ben through all the tutorials for several weeks.. and still getting nowhere..

anyway.. tried to create a datelink

datelink:

Load
LT,
Fin_Month as CommonDate,
'Common' as DateType
Resident financials;

no errors on this one..

however for Snapshot Mont

should it be 'Common' as DateType as well?

[#4]

here is what I have now.. I now have duplicate dates

tempcal:

Load
Date([Snapshot Month]) as CommonDate
Resident weekly_outstanding;
Concatenate
Load
Date(Fin_Month) as CommonDate
Resident financials;



Temp:
LOAD
Min(CommonDate) as MinDate,
Max(CommonDate) as MaxDate
Resident tempcal;

Let varMinDate = Num(Peek('MinDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('MaxDate', 0, 'Temp'));

TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

mastercalendar:
Load
Date(monthend(TempDate),'YY-MMM-DD') as CommonDate,
Year(TempDate) as CommonYear,
Month(TempDate) as CommonMonth
Resident TempCalendar;
DROP Table TempCalendar;

datelink:
Load
LT,
Fin_Month as CommonDate,
'Financial' as DateType
Resident financials;
Load
LT,
Fin_Month as CommonDate,
'Risk' as DateType
Resident financials;

Fin_Month	Snapshot Month	CommonDate
12/31/2014	01/31/2015	02/28/2015
12/31/2014	02/28/2015	02/28/2015
12/31/2014		02/28/2015
01/31/2015	01/31/2015	02/28/2015
01/31/2015	02/28/2015	02/28/2015
01/31/2015		02/28/2015
02/28/2015	01/31/2015	02/28/2015
02/28/2015	02/28/2015	02/28/2015
02/28/2015		02/28/2015

[jpenuliar]

Hi Lanie,

I noticed that all values in your Cust_Month,Fin_Month and Snapshop Month are all the same dates.

The solution I came up with is to make an Alias of all 3 fields in their respective Load script.

Cust_Month as Date

Fin_Month as Date

[Snapshot Month] as Date

This should work, otherwise, you will have to consider reviewing your Data Model