How to join two tables with a synthetic key using Interval Match&Join

[trishita]

I have two tables and I want to join them with 2 keys(IMO number and Date)

Sounding Correction:

LOAD

IMO_NUMBER AS IMO_NO,
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,

FROM
[mo_purch_sounding_correction_view.qvd]
(qvd);

General Information:

LOAD

  
    num(SURVEY_DATE)   as SURVEY_DATE_NUM,
    SURVEY_DATE,
    IMO_NO
FROM
[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;

[YoussefBelloum · Accepted solution]

Hi,

this is not the answer to your question, but:

1. there is not LEFT OUTER JOIN  in Qlik

https://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/Scripting/ScriptPrefixes/Join...

2. the syntax of the INTERVALMATCH is incorrect

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/ScriptPrefixes...

[trishita]

Apart from the left outer join syntax, can you explain what is wrong with my code?

General Information:

LOAD

  
    num(SURVEY_DATE)   as SURVEY_DATE_NUM,
    SURVEY_DATE,
    IMO_NO
FROM
[MO_BUN_SUR_SURVEY_VIEW.qvd]
(qvd);

Sounding Correction:

LOAD

IMO_NUMBER AS IMO_NO,
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,

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;

[YoussefBelloum]

EDITED

Like what i Said above, the IntervalMatch syntax is wrong and also you're trying to use QLIK syntax with SQL syntax (maybe)

because you use Intervalmatch (which is a Qlik function), so need a LOAD to be recognized, and also you used SELECT, which is not Qlik statement..

here you want to associate a unique Date to each SOUNDING_START_DATE and SOUNDING_END_DATE

you will maybe need to change to code after the inner join to this:

Inner Join

LOAD

INTERVALMATCH (SURVEY_DATE)

SOUNDING_START_DATE,SOUNDING_END_DATE

resident [Sounding Correction];

[trishita]

Inner Join

IntervalMatch ( Time, ProductionLine )

LOAD Start, End, ProductionLine

Resident OrderLog;

This was given in the link you just gave..I wanted to join the two tables based on both the time and IMO_Number

[YoussefBelloum]

would you be able to attach sample app ? or create it and attach it to be able to test please ?

because I didn't mention Time field until your last message..

[trishita]

OK :I HAVE TWO TABLES AND I WANT TO JOIN THEM ON TWO FIELDS:both of the tables contain imo number and also want to join them on survey date from table General Information and Sounding start date and Sounding end date from Sounding Corrections. Ihave used the  above script and joined them.I will show u currently how the data model looks like and the data as well.

[trishita]

I used the following script and got data like this:

GeneralInformation :


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
[\\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_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')),'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;

[YoussefBelloum]

Sorry I'm stuck just like you.. maybe stalwar1‌ can take a look here ?

[trishita]

ok if its possible then sure