DateKey Column in Calendar Table

[#1]

Hi ,

I have "CLAIM" table with column "Claim_Creation_Date" , I have created Calendar table based on below script using "Claim_creation_date" and this table has DateKey column

I want to link Calendar table with CLAIM table based on DateKey but CLAIM table does not have DateKey column, please suggest how to create DateKey column in CLAIM table so that I can link Calendar Table with CLAIM based on DateKey

QuartersMap:

MAPPING LOAD * Inline [

Month, Q

1, Q1

2, Q1

3, Q1

4, Q2

5, Q2

6, Q2

7, Q3

8, Q3

9, Q3

10, Q4

11, Q4

12, Q4

];

MonthMap:

Mapping LOAD * Inline [

MonthNo, LongMonth

1, January

2, February

3, March

4, April

5, May

6, June

7, July

8, August

9, September

10, October

11, November

12, December

];

Temp:

Load

min(Claim_Creation_Date) as minDate, // Replace DateKey with your Date key field

max(Claim_Creation_Date) as maxDate // Replace DateKey with your Date key field

Resident CLAIM; // Replace SourceTable with the name of the table containing the DateKey field

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

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

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Set vFM = 4 ;

MasterCalendar:

LOAD

TempDate AS DateKey, // Replace DateKey with your Date key field

week(TempDate) as Week,

Year(TempDate) as Year,

Year(TempDate) -1 as [PriorYear],

Month(TempDate) as Month,

Dual(ApplyMap('MonthMap', Month(TempDate), Null()), Month(TempDate)) as MonthLong,

Day(TempDate) as Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

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

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

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

WeekDay(TempDate) as WeekDay

// Fiscal Year fields

//Year(TempDate) + If(Month(TempDate)>=$(vFM), 1, 0) As [Fiscal Year],

//Mod(Month(TempDate)-$(vFM), 12)+1 As [Fiscal Month]

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

[sunny_talwar]

May be like this:

CLAIM:

LOAD AllFields,

          Claim_Creation_Date,

          Claim_Creation_Date as DateKey

FROM ....;

and then you MasterCalendar Script

[#3]

Hi Sunny,

DateKey column in Calendar table is numeric

"Claim_Creation_Date as DateKey" as mentioned above in your response will not create numeric Datekey column, it should be numeric column and consist of subset of values available in DateKey column in Calendar table so that I can link both tables

[sunny_talwar · Accepted solution]

DateKey may have a numeric representation but it still has a date representation as well. Same is true for the field I created below:

CLAIM:

LOAD AllFields,

          Claim_Creation_Date,

          Claim_Creation_Date as DateKey

FROM ....;

Unless Claim_Creation_Date is not TimeStamp, the correct association should be made

[#5]

Hi Sunny,

Thanks, I am able to link both tables based on your suggestion

Please also suggest the method which I have implemented for creating calendar is correct or not, can I use this method going forward in other projects