Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

trishita
Contributor 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
YoussefBelloum
Esteemed Contributor

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

9 Replies
YoussefBelloum
Esteemed Contributor

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

trishita
Contributor III

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

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
Esteemed Contributor

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

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
Contributor III

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

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
Esteemed Contributor

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

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
Contributor III

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

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
Contributor III

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

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
Esteemed Contributor

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

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

trishita
Contributor III

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

ok if its possible then sure