Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
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

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.

View solution in original post

12 Replies

Just rename the SURVEY_DATE_NUM  in the calendar table

SURVEY_DATE_NUM  as Calendar_SURVEY_DATE_NUM


that will resolve the issue

Creator II
Creator II

Or may be create a link table.

Please check this -

Concatenate vs Link Table

Creator III
Creator III

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

Creator III
Creator III

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

Creator III
Creator III

The document is already attached.

Creator III
Creator III

is there any other solution as there are already other tables

Creator III
Creator III

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

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.

View solution in original post

Creator III
Creator III

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