Synthetic key removal

[trishita]

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

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

Or may be create a link table.

Please check this -

Concatenate vs Link Table

[trishita]

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]

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

[trishita]

The document is already attached.

[trishita]

is there any other solution as there are already other tables

[trishita]

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 · Accepted solution]

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,

[trishita]

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