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

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

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

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;

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

YoussefBelloum — Wed, 09 May 2018 10:23:03 GMT

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.htm

2. the syntax of the INTERVALMATCH is incorrect

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

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

trishita — Wed, 09 May 2018 10:39:09 GMT

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

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;

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

YoussefBelloum — Wed, 09 May 2018 12:25:51 GMT

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];

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

trishita — Wed, 09 May 2018 13:46:08 GMT

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

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

YoussefBelloum — Wed, 09 May 2018 14:03:28 GMT

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

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

trishita — Wed, 09 May 2018 14:40:37 GMT

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.

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

trishita — Wed, 09 May 2018 14:42:17 GMT

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;

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

YoussefBelloum — Wed, 09 May 2018 14:55:15 GMT

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

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

trishita — Wed, 09 May 2018 15:23:30 GMT

ok if its possible then sure