Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Can you post the complete script?
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