How to join a third table with an already existing join from two tables

[trishita]

I want to join third table Summary with an existing join between two tables.,General Information & Sounding Correction:

General Information:

LOAD*
FROM
[MO_BUN_SUR_SURVEY_VIEW.qvd]
(qvd);

Sounding Correction:

LOAD *

FROM
[mo_purch_sounding_correction_view.qvd]
(qvd);

Inner Join

INTERVALMATCH (SURVEY_DATE,IMO_NO) SELECT SOUNDING_START_DATE,SOUNDING_END_DATE,IMO_NO

resident Sounding Correction;

This is the first join.

I need to join the results of the existing join with another table.(Summary)

To be precise there no common key between Summary and Sounding Correction but between General Information and Summary we have Survey ID:

I will upload the current result after the first join was executed between Sounding Correction, General Information

[jerifortune]

Is there any issue joining General Information Table with Summary table using the common IDs (SurveyID)? As long as there is an association between Sounding correction and General Information, then your model should work.

[trishita · Accepted solution]

Firstly:

  I have one problem that there are 2 synthetic keys created .In 1 table which contains only IMO No and Survey ID and the other synthetic key table which contains all other fields.As I am new to Qlikview is it advisable to drop the other synthetic keys which are not the main keys?

[datanibbler]

Hi,

a synthetic key in itself is not always a problem - it merely looks kind of unorderly - but if the one key you have is enough to link those two tables, I would advise to drop them by renaming one of the fields or qualifying the table in the LOAD.

Then joining the third table should not be a problem - depending on how many fields from the 3rd table you need. If it's just three or four fields, mapping those would probably be a lot faster.

HTH

Best regards,

DataNibbler

[trishita]

a) So if my code runs like this:

General Information:

LOAD*
FROM
[MO_BUN_SUR_SURVEY_VIEW.qvd]
(qvd);

Sounding Correction:

LOAD *

FROM

BUNKER_DELIVERY_NOTE_NUMBER,

DENSITY,

DIFFERENTIAL_REASON,

FUEL_MASS_AFTER_SOUNDING,

FUEL_MASS_BEFORE_SOUNDING,

(FUEL_MASS_AFTER_SOUNDING-FUEL_MASS_BEFORE_SOUNDING) AS DEVIATION_AFTER_SOUNDING,

FUEL_TYPE,

IMO_NUMBER AS IMO_NO, //Required Key

LOWER_HEATING_VALUE,

SOUNDING_TIME_TIMEZONE,

SOUNDING_TIME_UTC,

Date(Floor(Timestamp#(SOUNDING_TIME_UTC,'YYYY-MM-DD hh:mm')),'YYYY-MM-DD') AS SOUNDING_START_DATE,                                                                      //Required Key

Date(Floor(Timestamp#(SOUNDING_TIME_UTC, 'YYYY-MM-DD hh:mm'))+7, 'YYYY-MM-DD') AS SOUNDING_END_DATE,                                                                           //Required Key

SULPHUR_CONTENT,

TEMPERATURE,

VISCOSITY,

WATER_CONTENT

[mo_purch_sounding_correction_view.qvd]
(qvd);

Inner Join

INTERVALMATCH (SURVEY_DATE,IMO_NO) SELECT SOUNDING_START_DATE,SOUNDING_END_DATE,IMO_NO

resident Sounding Correction;

Where should I incorporate the code for Dropping the synthetic keys (all the other columns excluding the required key fields(SOUNDING_START_DATE,SOUNDING_END_DATE,IMO_NO ) from Sounding Correction