3 Replies Latest reply: Apr 6, 2010 10:45 AM by Upendra Mulumudi RSS

    Avoiding Synthetic Keys - Several tables with several fields in common

      Hello!

      I have a problem with Synthetic tables, due to the fact that I have several tables with several field in common.

      The tables and the fields are:

       

      TankerRatesDirtySpot:

       

       

      LOAD [Week - year] as RatesDate,

      Port,

      DWT,

      [DIRTY (Spot WS)] as WS,

      Day([Week - year]) as Day,

      Week([Week - year]) AS Week,

      Month([Week - year]) as Month,

      Year([Week - year]) as Year,

      Date(monthstart([Week - year]), 'MMM-YYYY') AS MonthYear



       

      TankerRatesCleanSpot:

       

       

      LOAD [Week - year] as RatesDate,

      Port,

      DWT,

      [CLEAN (Spot WS)] as WS,

      Day([Week - year]) as Day,

      Week([Week - year]) AS Week,

      Month([Week - year]) as Month,

      Year([Week - year]) as Year,

      Date(monthstart([Week - year]), 'MMM-YYYY') AS MonthYear



       

      TCRates:

       

       

      LOAD [Week - year] as RatesDate,

      Type,

      DWT,

      [TC Rates] as TCRates,

      Day([Week - year]) as Day,

      Week([Week - year]) AS Week,

      Month([Week - year]) as Month,

      Year([Week - year]) as Year



       

      NewBuildingPrices:

       

       

      LOAD [Week - year],

      Type,

      DWT,

      Prices,

      Date(monthstart([Week - year]), 'MMM-YYYY') AS MonthYear,

      Day([Week - year]) as Day,

      Week([Week - year]) AS Week,

      Month([Week - year]) as Month,

      Year([Week - year]) as Year



       

      TankOrderbook:

       

       

      LOAD [BUILT YEAR] as BuiltYear,

      [BUILT MONTH],

      [YARD COUNTRY] as YardCountry,

      DWT,

      TYPE as Type,

      [STATUS GROUP] as StatusGroup,

      [SHIP TYPE] as ShipType,

      [SHIP GROUP] as ShipGroup,

      OWNER as Owner,

      [ORDERBOOK ENTRY DATE] as OrderbookEntryDate,

      Day([ORDERBOOK ENTRY DATE]) as Day,

      Week([ORDERBOOK ENTRY DATE]) AS Week,

      Month([ORDERBOOK ENTRY DATE]) as Month,

      Year([ORDERBOOK ENTRY DATE]) as Year,

      Year([ORDERBOOK ENTRY DATE]) as OrderbookEntryYear,

      Date(monthstart([ORDERBOOK ENTRY DATE]), 'MMM-YYYY') AS OrderEntryMonthYear,

      [BUILT YEAR] - Year([ORDERBOOK ENTRY DATE])as Durations



       

      AfraShipments:

       

       

      LOAD LLPNR,

      [SHIP NAME]as ShipName,

      BUILT as Built,

      DWT,

      [DWT Type] as Type,

      [SHIP TYPE] as ShipSegment,

      [FROM PLACE DATE]as FromPlaceDate,

      [TO PLACE DATE]as ToPlaceDate,

      if([TO PLACE DATE]-[FROM PLACE DATE] >= 0,[TO PLACE DATE]-[FROM PLACE DATE]) as ShipmentDuration,

      Day([FROM PLACE DATE]) as Day,

      Week([FROM PLACE DATE]) AS Week,

      Month([FROM PLACE DATE]) as Month,

      Year([FROM PLACE DATE])as Year,

      Date(monthstart([FROM PLACE DATE]), 'MMM-YYYY') AS FromMonthYear,

      Date(monthstart([TO PLACE DATE]), 'MMM-YYYY') AS ToMonthYear,

      if([TO PLACE DATE]-[FROM PLACE DATE] < 0,[TO PLACE DATE]-[FROM PLACE DATE]) as ShipmentDurationError,

      if(isNull([FROM PLACE DATE]),'FromPlaceDateIsBlank') as BlankFromPlaceDate,

      if(isNull([TO PLACE DATE]),'ToPlaceDateIsBlank') as BlankToPlaceDate,

      if(isNull([TO PLACE DATE]) and isNull([FROM PLACE DATE]), 'BothDatesAreNull') as BlankToAndFromDates,

      if([TO PLACE DATE] < [FROM PLACE DATE], 'ToDatePriorFromDate') as ToPlaceDateError



       

      What I'm trying to do is, for example, when I select a the Year 2005 this selection will affect all the tables in my analyse, since all of them have date fields. Or, for example, when I select a ship type, that one that was selected will be selected in all tables that have the Type field.

       

      Maybe this is a basic question, but i'm really struggling with this!

       

      I hope anyone can help me!

       

      Thanks in advance!