How to perform interval match and join on two data fields between two dataviews

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

I want to join two tables on the basis of two unique fields, IMO Number and A specific Date on the basis of interval match function and left outer join.

Q1.I have written the code.Can anyone verify if my approach is right or not

Q2.Do I need to take care of the Extended IntervalMatch function or not

Sounding Correction:

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')),'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
[mo_purch_sounding_correction_view.qvd]
(qvd);

General Information:

LOAD SURVEY_ID,

    SURVEY_ID as SURVEY_ID_General_Information,
    num(SURVEY_DATE)   as SURVEY_DATE_NUM,
    SURVEY_DATE,
    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,
    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);

LEFT OUTER JOIN INTERVALMATCH (SURVEY_DATE,IMO_NO) SELECT SOUNDING_START_DATE,SOUNDING_END_DATE,IMO_NO

resident Sounding Correction;

topic How to perform interval match and join on two data fields between two dataviews in QlikView

How to perform interval match and join on two data fields between two dataviews