topic Re: How to join tables without any specific unique key in QlikView

How to join tables without any specific unique key

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

I need to join two tables in qlikview but they don't have any specific common key to join.what could be the possible solution for it?

The Bunkering and NI come from different applications.

1. Bunkering oil total view:

LOAD SURVEY_ID,

SURVEY_CONDITION,

TANK_TOTAL_AMOUNT,

OIL_KIND,

OIL_TOTAL_OBS_VOLUME,

OIL_TOTAL_GOV,

OIL_TOTAL_MASS,

OIL_TOTAL_WEIGHT

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_oil_total_view.qvd]

(qvd);

2. NI Oil Consumption View

LOAD COMBINED_KEY,

OIL_ID,

OC_CONSUMPTIVE_ENGINE_ID,

OC_MASS,

OC_VALID_FROM_UTC,

OC_VALID_FROM_TIMEZONE,

OC_VALID_TO_UTC,

OC_VALID_TO_TIMEZONE

FROM

[\\ww.hl.lan\HH1\Groups\RHH0T432 - ROB project\14 - QlikView\Test QVDs Navigator Insight\MO_NI_OIL_CONSUMPTION_VIEW.qvd]

(qvd);

If I want to join two such tables where is no common primary or unique key,what could be the possible solution.

OIL KIND AND OIL ID are somewhat similar but it is not unique.But the combined key in NI Oil Consumption View has an IMO number which is in a database table view from another bunkering table view. given below(Bunkering Survey Table)

LOAD SURVEY_ID,

//SURVEY_ID as SURVEY_ID_General_Information,

num(SURVEY_DATE) as SURVEY_DATE_NUM,

IMO_NO,

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,

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

Re: How to join tables without any specific unique key

bc-thebruuu — Wed, 25 Apr 2018 12:24:17 GMT

Could you send us a schema of the tables availables and the expected result?

Re: How to join tables without any specific unique key

YoussefBelloum — Wed, 25 Apr 2018 12:25:26 GMT

Hi,

if OIL KIND and OIL ID are "similar" but not unique, it is a key.

didi you try to join using that field ?

you just have to rename these two fields with the same name on both table so that Qlik engine can make the join.

Re: How to join tables without any specific unique key

vvira1316 — Wed, 25 Apr 2018 12:29:08 GMT

There are several points here that you may want to look into.

What is the business reason to join them?

What are other tables in the data model that may provide alternatives/support to include these two tables.

You may want to reach out to business/team for better understanding of dashboard functionality and data supporting it.

Re: How to join tables without any specific unique key

trishita — Wed, 25 Apr 2018 14:08:21 GMT

no its no key..they are just strings and type of oil which are not unique

Re: How to join tables without any specific unique key

YoussefBelloum — Wed, 25 Apr 2018 14:18:46 GMT

ok, what I can recommend here is the same as vvira1316 already recommend.

what you can do more is maybe install Qliksense desktop and try its data manager, it can be really helpful to understand your data