Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Third Table is not linked to the Bridge,
Another trick is a "Date Island" which you can use,
But I prefer the rwunderlich technique.
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?
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 |
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