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

Synthetic key removal

I want to get rid of the synthetic key in my qvd:I only want the link between CALENDER and GENERAL INFORMATION TABLE to be SURVEYDATE

1 Solution

Accepted Solutions
PrashantSangle

try with below code check bold letter code.

LOAD SURVEY_ID,

     //SURVEY_ID as SURVEY_ID_General_Information,

     SURVEY_DATE,

     //num(SURVEY_DATE)         as SURVEY_DATE_NUMBER,

//    month("SURVEY_DATE")             as "Month",

//    QuarterName("SURVEY_DATE")     as "Quarter",

    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,

    //"ORDER_REF_NO",

    //"BUNKER_DELIVERY_NOTE",

    //"BUNKER_SUPPLIER",

    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

[$(vG.QVDPath)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'))-2,'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;

Join (General_Information)

LOAD *

Resident SoundingCorrections;

DROP Table SoundingCorrections;

--------------------------------

CALENDER.

tmp:

load Min(num(SURVEY_DATE)) as MinDate,

     Max(num(SURVEY_DATE)) as MaxDate

Resident General_Information;

LET vMinDate = peek('MinDate',0,'tmp');

LET vMaxDate = peek('MaxDate',0,'tmp');

drop Table tmp;

Datefield:

LOAD

$(vMinDate) + IterNo() -1 as Datefield

AUTOGENERATE (1)

WHILE $(vMinDate) + IterNo() -1 <= $(vMaxDate);

Calendar:

LOAD

Datefield                         as SURVEY_DATE_NUM,

date(Datefield,'DD.MM.YYYY')    as SURVEY_DATE,

year(Datefield)                 as SURVEY_YEAR,

QuarterName(Datefield)             as SURVEY_QUARTER_TEXT,

'Q' & ceil(month(Datefield)/3)    as SURVEY_QUARTER,

month(Datefield)                 as SURVEY_MONTH,

day(Datefield)                     as SURVEY_DAY,

week(Datefield)                 as SURVEY_WEEK,

weekday(Datefield)                 as SURVEY_WEEKDAY

RESIDENT Datefield;

drop table Datefield;

let vMinDate=;

let vMaxDate=;

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

12 Replies
avinashelite

Just rename the SURVEY_DATE_NUM  in the calendar table

SURVEY_DATE_NUM  as Calendar_SURVEY_DATE_NUM


that will resolve the issue

Neymar_Jr
Creator II
Creator II

Or may be create a link table.

Please check this -

Concatenate vs Link Table

trishita
Creator III
Creator III
Author

The problem is i tried to change the name of the field survey date num but when i tried to reload it ,it failed.

kfoudhaily
Partner - Creator III
Partner - Creator III

please provide the peace of your script where you define this field

QlikView Qlik Sense consultant
trishita
Creator III
Creator III
Author

The document is already attached.

trishita
Creator III
Creator III
Author

is there any other solution as there are already other tables

trishita
Creator III
Creator III
Author

General_Information:

LOAD SURVEY_ID,

     //SURVEY_ID as SURVEY_ID_General_Information,

     SURVEY_DATE,

     num(SURVEY_DATE)         as SURVEY_DATE_NUMBER,

//    month("SURVEY_DATE")             as "Month",

//    QuarterName("SURVEY_DATE")     as "Quarter",

    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,

    //"ORDER_REF_NO",

    //"BUNKER_DELIVERY_NOTE",

    //"BUNKER_SUPPLIER",

    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

[$(vG.QVDPath)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'))-2,'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;

Join (General_Information)

LOAD *

Resident SoundingCorrections;

DROP Table SoundingCorrections;

--------------------------------

CALENDER.

tmp:

load Min(SURVEY_DATE_NUM) as MinDate,

     Max(SURVEY_DATE_NUM) as MaxDate

Resident General_Information;

LET vMinDate = peek('MinDate',0,'tmp');

LET vMaxDate = peek('MaxDate',0,'tmp');

drop Table tmp;

Datefield:

LOAD

$(vMinDate) + IterNo() -1 as Datefield

AUTOGENERATE (1)

WHILE $(vMinDate) + IterNo() -1 <= $(vMaxDate);

Calendar:

LOAD

Datefield                         as SURVEY_DATE_NUM,

date(Datefield,'DD.MM.YYYY')    as SURVEY_DATE,

year(Datefield)                 as SURVEY_YEAR,

QuarterName(Datefield)             as SURVEY_QUARTER_TEXT,

'Q' & ceil(month(Datefield)/3)    as SURVEY_QUARTER,

month(Datefield)                 as SURVEY_MONTH,

day(Datefield)                     as SURVEY_DAY,

week(Datefield)                 as SURVEY_WEEK,

weekday(Datefield)                 as SURVEY_WEEKDAY

RESIDENT Datefield;

drop table Datefield;

let vMinDate=;

let vMaxDate=;

PrashantSangle

try with below code check bold letter code.

LOAD SURVEY_ID,

     //SURVEY_ID as SURVEY_ID_General_Information,

     SURVEY_DATE,

     //num(SURVEY_DATE)         as SURVEY_DATE_NUMBER,

//    month("SURVEY_DATE")             as "Month",

//    QuarterName("SURVEY_DATE")     as "Quarter",

    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,

    //"ORDER_REF_NO",

    //"BUNKER_DELIVERY_NOTE",

    //"BUNKER_SUPPLIER",

    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

[$(vG.QVDPath)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'))-2,'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;

Join (General_Information)

LOAD *

Resident SoundingCorrections;

DROP Table SoundingCorrections;

--------------------------------

CALENDER.

tmp:

load Min(num(SURVEY_DATE)) as MinDate,

     Max(num(SURVEY_DATE)) as MaxDate

Resident General_Information;

LET vMinDate = peek('MinDate',0,'tmp');

LET vMaxDate = peek('MaxDate',0,'tmp');

drop Table tmp;

Datefield:

LOAD

$(vMinDate) + IterNo() -1 as Datefield

AUTOGENERATE (1)

WHILE $(vMinDate) + IterNo() -1 <= $(vMaxDate);

Calendar:

LOAD

Datefield                         as SURVEY_DATE_NUM,

date(Datefield,'DD.MM.YYYY')    as SURVEY_DATE,

year(Datefield)                 as SURVEY_YEAR,

QuarterName(Datefield)             as SURVEY_QUARTER_TEXT,

'Q' & ceil(month(Datefield)/3)    as SURVEY_QUARTER,

month(Datefield)                 as SURVEY_MONTH,

day(Datefield)                     as SURVEY_DAY,

week(Datefield)                 as SURVEY_WEEK,

weekday(Datefield)                 as SURVEY_WEEKDAY

RESIDENT Datefield;

drop table Datefield;

let vMinDate=;

let vMaxDate=;

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
trishita
Creator III
Creator III
Author

@PrashantSangleThanks a lot..It did the trick...