topic Re: calendar - synthetic key issue in App Development

calendar - synthetic key issue

joeybird — Wed, 09 Sep 2015 15:27:10 GMT

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

Re: calendar - synthetic key issue

Gysbert_Wassenaar — Wed, 09 Sep 2015 16:49:36 GMT

Can you post the complete script?

Re: calendar - synthetic key issue

joeybird — Wed, 09 Sep 2015 18:48:35 GMT

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

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