Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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!

3 Replies
Not applicable
Author

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.

Not applicable
Author

The sources are simple excel sheets...

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

vupen
Partner - Creator
Partner - Creator

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