calendar - synthetic key issue

[joeybird]

HI

I derived a calendar as follows

Calendar: DECLARE FIELD DEFINITION TAGGED '$date'
Parameters first_month_of_year = 1,
fiscal_first_month_of_year = 4

Fields

    Year($1) As Year Tagged '$year',
    
    if( Month($1)>=4, Year($1), Year($1) -1) as FiscalYear Tagged '$fiscalyear', 
    
    Month($1) as Month Tagged '$month',
    
    if( Month($1)>=4, Month($1)-3,Month($1)+9) as FiscalMonthNumber Tagged '$FiscalMonthNumber',
    
    Date($1) as Date Tagged ('$date', '$day'),

    Week($1) as Week Tagged '$week',

    Weekday($1) as Weekday Tagged '$weekday',

    'Q' & Ceil(Month($1)/3)  as FinancialQuarter Tagged '$FinancialQuarter';
    

   DERIVE FIELDS FROM FIELDS [OrderDate] USING Calendar;

table one contains OrderDate

table two contains ProcessDate as OrderDate

I have two tables that needs to link to this calendar but its causing a synthetic key.

is there a way around it?

Kind Regards

JoeyBird

[Gysbert_Wassenaar]

Can you post the complete script?

[joeybird]

Hiya

i cant because of the data it contains..

i can only give brief example

Table 1

CustomerID

OrderDate

i go on to use the OrderDate to define calendar fields

Calendar: DECLARE FIELD DEFINITION TAGGED '$date'
Parameters first_month_of_year = 1,
fiscal_first_month_of_year = 4

Fields

    Year($1) As Year Tagged '$year',
    
    if( Month($1)>=4, Year($1), Year($1) -1) as FiscalYear Tagged '$fiscalyear', 
    
    Month($1) as Month Tagged '$month',
    
    if( Month($1)>=4, Month($1)-3,Month($1)+9) as FiscalMonthNumber Tagged '$FiscalMonthNumber',
    
    Date($1) as Date Tagged ('$date', '$day'),

    Week($1) as Week Tagged '$week',

    Weekday($1) as Weekday Tagged '$weekday',

    'Q' & Ceil(Month($1)/3)  as FinancialQuarter Tagged '$FinancialQuarter';
   

   DERIVE FIELDS FROM FIELDS [OrderDate] USING Calendar;

next table contains

Table 2

ShipperID

ProcessDate as OrderDate  // I have to go this to get the field to link to the calendar fields but it causes synthtic key

Please help

joeybird x