SO MY EXISTING SCRIPT IS LIKE THIS. Where can I add the join between SURVEY_ID_Fuel_Type_ID from summary and SURVEY_ID & '|' NIFuelType AS SURVEY_ID_NIFuel_Type_ID in general information
General_Information:
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;
----------------------------------------------------------------------------------------------------------------
MAP1:
//MAPPING
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;
----------------------------------------------------------------------------------------------------------------
Summary:
//left join (General_Information)
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)
;
//left join (General_Information)
//LOAD *
//Resident Summary;
////DROP Table SoundingCorrections;
I need to add up the values for the the same kind of fuel type for Computed rob and Actual ROB field..Like DMA SHOULD SHOW ONLY 1 VALUE And so will RMD 80.Only one value per fuel type.The summed expression.
