topic Summing up oil type values in QlikView

Summing up oil type values

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

I need to add up the values for the the same kind of fuel type for Computed rob and Actual ROB field..Like DMA SHOULD SHOW ONLY 1 VALUE And so will RMD 80.Only one value per fuel type.The summed expression.

Re: Summing up oil type values

sunny_talwar — Fri, 22 Jun 2018 12:44:49 GMT

Is Differential added as a dimension?

May be remove that as one of the dimensions.

Re: Summing up oil type values

trishita — Fri, 22 Jun 2018 12:46:11 GMT

it can be removed..its not mandatory...

Re: Summing up oil type values

sunny_talwar — Fri, 22 Jun 2018 12:50:05 GMT

Then try to remove it and see if you get a single row?

Re: Summing up oil type values

trishita — Fri, 22 Jun 2018 13:02:56 GMT

My problem is a bit different.Previously I developed a ample code and it was working fine.Test qvd

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

But when I integrated with a complex module which I need for my final development,some records were missing

Re: Summing up oil type values

sunny_talwar — Fri, 22 Jun 2018 13:13:03 GMT

There are 4 tables in the above screenshot with the new model... which one am I looking at?

Re: Summing up oil type values

trishita — Fri, 22 Jun 2018 13:16:48 GMT

Re: Summing up oil type values

sunny_talwar — Fri, 22 Jun 2018 13:28:05 GMT

Would you be able to share a sample to look at this?

Re: Summing up oil type values

trishita — Fri, 22 Jun 2018 13:34:45 GMT

ok i would first there the dummy file which has the proper report (which is working fine) Navigator_Insight_Sounding_Correction _Trishita_Test1.qvw

The second one is the main file where I want to integrate my changes and eventualy the records are missing

Remaining on Board DM _SoundingCorrection_Trishita_2206.

Re: Summing up oil type values

sunny_talwar — Fri, 22 Jun 2018 13:51:15 GMT

I think you need a better key that joins Summary table with General_Information. SURVEY_ID is to generic an causing Cartesian product for this to not work.

Have you thought about connecting them on SURVEY_ID_Fuel_Type_ID and SURVEY_ID AND NiFuelType?

Re: Summing up oil type values

trishita — Fri, 22 Jun 2018 13:57:40 GMT

The summary table was already joined in the main qvw.I did not do any changes.It is also connected with other table views

Re: Summing up oil type values

trishita — Fri, 22 Jun 2018 13:59:59 GMT

But I can try to join with Fuel Type of Summary and NI Fuel Type in General Informations along side SURVEY id..How should I join them?Can u help me with the query

Re: Summing up oil type values

trishita — Fri, 22 Jun 2018 14:32:34 GMT

SO MY EXISTING SCRIPT IS LIKE THIS. Where can I add the join between SURVEY_ID_Fuel_Type_ID from summary and SURVEY_ID & '|' NIFuelType AS SURVEY_ID_NIFuel_Type_ID in general information

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;

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

MAP1:

//MAPPING

LOAD *,

//SURVEYID,

if(WildMatch([FUEL_TYPE], 'DM*') and SULPHUR_CONTENT >.01,'HSDO', //// IF FUEL TYPE BEGINS WITH DM AND HAS SULPHUR CONTENT GREATER THAN =.01,IT SHOULD BE MAPPED TO HSDO

if(WildMatch([FUEL_TYPE], 'DM*') and SULPHUR_CONTENT <.01,'LSDO', // IF FUEL TYPE BEGINS WITH DM AND HAS SULPHUR CONTENT GREATER THAN =.01,IT SHOULD BE MAPPED TO LSDO

if(WildMatch([FUEL_TYPE], 'RM*') and SULPHUR_CONTENT >.01,'HSFO', // IF FUEL TYPE BEGINS WITH RM AND HAS SULPHUR CONTENT GREATER THAN .01,IT SHOULD BE MAPPED TO HSFO

if(WildMatch([FUEL_TYPE], 'RM*') and SULPHUR_CONTENT <.01 ,'ULSFO')))) // IF FUEL TYPE BEGINS WITH RM AND HAS SULPHUR CONTENT LESSER THAN .01,IT SHOULD BE MAPPED TO ULSFO

as NIFuelType

Resident General_Information;

drop Table General_Information;

RENAME Table MAP1 to General_Information;

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

Summary:

//left join (General_Information)

LOAD SURVEY_ID,

//SURVEY_ID as SURVEY_ID_Summary,

SURVEY_ID & '|' & OIL_KIND AS SURVEY_ID_Fuel_Type_ID,

OIL_KIND as "Fuel Type",

OIL_SUM_LAST_NOON as "Fuel per last Noon",

OIL_SUM_MOST_RECENT as "Fuel per most recent Report",

OIL_SUM_SURVEYED as "Fuel surveyed",

OIL_SUM_DIFFERENCE as "Difference Log vs Survey"

FROM

[$(vG.QVDPath)MO_BUN_SUR_OIL_SUMMARY_VIEW.qvd]

(qvd)

where Exists(SURVEY_ID)

;

//left join (General_Information)

//LOAD *

//Resident Summary;

////DROP Table SoundingCorrections;

Re: Summing up oil type values

sunny_talwar — Fri, 22 Jun 2018 14:33:15 GMT

Made some changes to the app's script... see if this works

Re: Summing up oil type values

trishita — Fri, 22 Jun 2018 15:00:40 GMT

its not opening anyhow

Re: Summing up oil type values

sunny_talwar — Fri, 22 Jun 2018 15:14:26 GMT

Here is just the script

Re: Summing up oil type values

trishita — Wed, 27 Jun 2018 09:40:18 GMT

Hi Sunny,

i saw the script but could you let me know where u did the change, cause its quiet difficult for me to identify

Re: Summing up oil type values

sunny_talwar — Wed, 27 Jun 2018 11:01:10 GMT

Because it has been few days, it will take me some time to find it out what I changed.... why don't you copy and paste the whole script and run it?

Re: Summing up oil type values

trishita — Wed, 27 Jun 2018 11:16:33 GMT

Ya I did it, and unfortunalely its like the

Summary:

LOAD //SURVEY_ID,

//SURVEY_ID as SURVEY_ID_Summary,

SURVEY_ID & '|' & OIL_KIND AS SURVEY_ID_Fuel_Type_ID,

OIL_KIND as "Fuel Type",

OIL_SUM_LAST_NOON as "Fuel per last Noon",

OIL_SUM_MOST_RECENT as "Fuel per most recent Report",

OIL_SUM_SURVEYED as "Fuel surveyed",

OIL_SUM_DIFFERENCE as "Difference Log vs Survey"

FROM

[$(vG.QVDPath)MO_BUN_SUR_OIL_SUMMARY_VIEW.qvd]

(qvd)

where Exists(SURVEY_ID)

;

I remember what you changed but as the survey id was commented out, all the links with existing table was destroyed and so many other cloumns did not populate..Could you tell me like how can i join the column NIFUELTYPE AND FUELTYPE or how can we incorporate nifueltype into the summary table

Re: Summing up oil type values

sunny_talwar — Wed, 27 Jun 2018 11:17:51 GMT

Would you be able to repost your app after re-running the dashboard. if there is an error, can you post the screenshot of the error?