Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Hi
I think with this many tables I would probably implement a link table to sit in the middle of a star schema, but it's difficult for me to go any further with any advice unless I know the source of these tables, is this SQL and if so can you provide the SQL Statements that go with each table.
Thanks.
The sources are simple excel sheets...
How can I build that link table? I am not sure if I understand what do you mean...
Follow the below steps to avoid synthetic keys:
1. Concatenate the columns you want to use to link two tables, and create a new column. Ex.: COLUMNA & '_' & COLUMNB
2. Rename the column in one of the tables to a different name.
3. Best step is to start with columns used by QV in creating the Synthetic Keys.
Upendra