Problems joining two tables.

trishita — Wed, 25 Nov 2020 16:16:04 GMT

I want to join two tables General Information and Summary...I am having problem joining them as data is not populating.The script is as follow. The problem is the general information and summary is already joined in an exisitng script with SURVEY_ID.Now I introduced a new field in General Information as NIFuelType and want to join it with Fuel Type of Summary.The both tables are also joined with other tables with the same SURVEY_ID

LOAD SURVEY_ID,

//SURVEY_ID as SURVEY_ID_General_Information,

SURVEY_DATE,

// num(SURVEY_DATE) as SURVEY_DATE_NUMBER,

// month("SURVEY_DATE") as "Month",

// QuarterName("SURVEY_DATE") as "Quarter",

IMO_NO,

Upper (SHIP_NAME) as SHIP_NAME,

OWNER,

KIND as SURVEY_TYPE,

VOYAGE_NO,

TERMINAL_NAME,

Upper (FLAG) as Flag,

CHIEF_ENGINEER_NAME,

CREW_MANAGER,

LOG_BOOK_LAST_NOON,

LOG_BOOK_MOST_RECENT,

MASTER_NAME,

OIL_BOOK_RECORD,

Upper (OPERATOR) as OPERATOR,

IS_OUTSIDE_PORT,

SURVEY_PORT,

OUTSIDE_PORT_LOCATION,

SURVEY_DURATION,

SURVEY_VERSION,

//"ORDER_REF_NO",

//"BUNKER_DELIVERY_NOTE",

//"BUNKER_SUPPLIER",

COMMENT,

Upper("SURVEYOR_NAME") as Surveyor_NAME,

SURVEY_COMPANY_NAME,

SURVEY_COMPANY_CITY,

SURVEY_COMPANY_ADDRESS,

SURVEY_COMPANY_EMAIL,

SURVEY_COMPANY_PHONE,

TEMPERATURE_ENGINE_ROOM,

TEMPERATURE_OUTSIDE,

TEMPERATURE_SEA_WATER,

DRAFT_AFT_AFTER,

DRAFT_AFT_BEFORE,

DRAFT_FWD_AFTER,

DRAFT_FWD_BEFORE,

DRAFT_MID_AFTER,

DRAFT_MID_BEFORE,

DRAFT_TRIM_AFTER,

DRAFT_TRIM_BEFORE,

AFTER_SND_LIST,

AFTER_SND_LOCATION,

BEFORE_SND_LIST,

BEFORE_SND_LOCATION

FROM

[$(vG.QVDPath)MO_BUN_SUR_SURVEY_VIEW.qvd]

(qvd);

SoundingCorrections:

Load

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,

LOWER_HEATING_VALUE,

SOUNDING_TIME_TIMEZONE,

SOUNDING_TIME_UTC,

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

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

SULPHUR_CONTENT,

TEMPERATURE,

VISCOSITY,

WATER_CONTENT

FROM

[\\ww.hl.lan\HH1\Groups\RHH0T432 - ROB project\14 - QlikView\COMPASS BIAC\RoB - QDF structure 01\QlikViewStorage\SourceDocuments\10.Operations\3.Remaining On Board\2.QVD\mo_purch_sounding_correction_view.qvd]

(qvd);

Inner Join

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

resident SoundingCorrections;

Join (General_Information)

LOAD *

Resident SoundingCorrections;

DROP Table SoundingCorrections;

-----------------------------------------------------------------------------------

///$tab Mapping Navigator Insight Fuel Type

MAP1:

LOAD *,

//SURVEYID,

if(WildMatch([FUEL_TYPE], 'DM*') and SULPHUR_CONTENT >.01,'HSDO', //// IF FUEL TYPE BEGINS WITH DM AND HAS SULPHUR CONTENT GREATER THAN =.01,IT SHOULD BE MAPPED TO HSDO

if(WildMatch([FUEL_TYPE], 'DM*') and SULPHUR_CONTENT <.01,'LSDO', // IF FUEL TYPE BEGINS WITH DM AND HAS SULPHUR CONTENT GREATER THAN =.01,IT SHOULD BE MAPPED TO LSDO

if(WildMatch([FUEL_TYPE], 'RM*') and SULPHUR_CONTENT >.01,'HSFO', // IF FUEL TYPE BEGINS WITH RM AND HAS SULPHUR CONTENT GREATER THAN .01,IT SHOULD BE MAPPED TO HSFO

if(WildMatch([FUEL_TYPE], 'RM*') and SULPHUR_CONTENT <.01 ,'ULSFO')))) // IF FUEL TYPE BEGINS WITH RM AND HAS SULPHUR CONTENT LESSER THAN .01,IT SHOULD BE MAPPED TO ULSFO

as NIFuelType

Resident General_Information;

drop Table General_Information;

RENAME Table MAP1 to General_Information;

-----------------------------------------------------------------------------------------------------------------

///$tab Summary

// This table contains the values which can be found under "Summary" in the ROB Report

// The fuel types as well as the logbook and surveyed values can be found here

Summary:

LOAD SURVEY_ID,

//SURVEY_ID as SURVEY_ID_Summary,

SURVEY_ID & '|' & OIL_KIND AS SURVEY_ID_Fuel_Type_ID,

OIL_KIND as "Fuel Type",

OIL_SUM_LAST_NOON as "Fuel per last Noon",

OIL_SUM_MOST_RECENT as "Fuel per most recent Report",

OIL_SUM_SURVEYED as "Fuel surveyed",

OIL_SUM_DIFFERENCE as "Difference Log vs Survey"

FROM

[$(vG.QVDPath)MO_BUN_SUR_OIL_SUMMARY_VIEW.qvd]

(qvd)

where Exists(SURVEY_ID);

topic Problems joining two tables. in QlikView

Problems joining two tables.