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);
