Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
trishita
Creator III
Creator III

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

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;

1 Solution

Accepted Solutions
9 Replies
YoussefBelloum
Champion
Champion

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
Creator III
Creator III
Author

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
Champion
Champion

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
Creator III
Creator III
Author

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
Champion
Champion

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
Creator III
Creator III
Author

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.DataModel.png

trishita
Creator III
Creator III
Author

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;



data.png

YoussefBelloum
Champion
Champion

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

trishita
Creator III
Creator III
Author

ok if its possible then sure