Avoiding Synthetic Keys - Several tables with several fields in common

Tue, 06 Apr 2010 13:40:12 GMT

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!

Avoiding Synthetic Keys - Several tables with several fields in common

Tue, 06 Apr 2010 14:02:00 GMT

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.

Avoiding Synthetic Keys - Several tables with several fields in common

Tue, 06 Apr 2010 14:07:05 GMT

The sources are simple excel sheets...

How can I build that link table? I am not sure if I understand what do you mean...

Avoiding Synthetic Keys - Several tables with several fields in common

vupen — Tue, 06 Apr 2010 14:45:39 GMT

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

topic Avoiding Synthetic Keys - Several tables with several fields in common in QlikView

Avoiding Synthetic Keys - Several tables with several fields in common

Avoiding Synthetic Keys - Several tables with several fields in common

Avoiding Synthetic Keys - Several tables with several fields in common

Avoiding Synthetic Keys - Several tables with several fields in common