12 Replies Latest reply: Jul 5, 2018 11:25 PM by Prashant Sangle RSS
      • Re: Synthetic key removal
        Avinash R

        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
          Rajesh Thakur

          Or may be create a link table.

           

          Please check this -

          Concatenate vs Link Table

          • Re: Synthetic key removal
            TRISHITA BANERJEE

            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
                Prashant Sangle

                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,

              • Re: Synthetic key removal
                TRISHITA BANERJEE

                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 BANERJEE

                  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