topic Re: Synthetic key removal in QlikView

Synthetic key removal

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

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

Re: Synthetic key removal

avinashelite — Tue, 12 Jun 2018 10:43:38 GMT

Just rename the SURVEY_DATE_NUM in the calendar table

SURVEY_DATE_NUM as Calendar_SURVEY_DATE_NUM

that will resolve the issue

Re: Synthetic key removal

Neymar_Jr — Tue, 12 Jun 2018 10:45:32 GMT

Or may be create a link table.

Please check this -

Concatenate vs Link Table

Re: Synthetic key removal

trishita — Tue, 12 Jun 2018 11:38:28 GMT

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

Re: Synthetic key removal

kfoudhaily — Tue, 12 Jun 2018 13:02:21 GMT

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

Re: Synthetic key removal

trishita — Tue, 12 Jun 2018 13:10:53 GMT

The document is already attached.

Re: Synthetic key removal

trishita — Tue, 12 Jun 2018 13:11:21 GMT

is there any other solution as there are already other tables

Re: Synthetic key removal

trishita — Tue, 12 Jun 2018 13:14:36 GMT

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

Re: Synthetic key removal

PrashantSangle — Tue, 12 Jun 2018 15:11:15 GMT

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

(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,

Re: Synthetic key removal

trishita — Tue, 19 Jun 2018 10:38:39 GMT

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

Re: Synthetic key removal

trishita — Thu, 05 Jul 2018 13:53:33 GMT

i have a chart table box.Did you mean in the expression? or in the load script?

Secondly what if i want to show only values more than 10 and lesser than -10

Re: Synthetic key removal

trishita — Thu, 05 Jul 2018 13:56:57 GMT

i want to pick the records for which i have this data ie all records where diff vs log is greater than 10.Not just displaying greater than 10 or not

Re: Synthetic key removal

PrashantSangle — Fri, 06 Jul 2018 03:25:14 GMT

Open new thread with detail explanation, so that other can also help you.

Regards,