topic Re: <Question> Difficult conditional expression (aggr?) in QlikView

Difficult conditional expression (aggr?)

n1ef5ng1 — Tue, 04 Mar 2014 09:46:11 GMT

Hi all, attached file is a sample file that has few linkage of the table.

An overview of the industry, this dataset is a transportation hub where we move boxes from vessel to another vessel once it arrived at our location. When a vessel reach to our shore, the vessel will either discharge boxes to us or load boxes to the vessel as known as activity_code in our database.

An overview of the table structure , Data table contains each individual record of boxes being moving around either in Load or Disc. Each vessel will have multiple records in this table, this tabls is where we are able to capture the volume

Vessel is the details of the vessel capacity, the maximum volume it can handle

service and calandar is a dimension table

TX is the data table where the volume of boxes that are left on the vessel.

An example : a vessel that has a capacity of 600 will be discharging 200 TEU from the veseel and load 100 TEU to the vessel. However, there is 300 TEU still on board of the vessel remain untouch by us.

in that case, data table will have Discharge data of 200, Load data of 100 and TX table will have 300

The problem is we would want to find out how utilize the vessel is , in the above example. we would like to come out a formula to find out how fill up of boxes is on the vessel.

Formula is = ((Load TEU + Disc TEU) + (TX TEU *2)) / (Capacity *2) <-- can be found on sample Vessel Utilization (Help) Table

On vessel details table, these are the records of the vessels, note that each vessel name comes in multiple time within a month. The way we differentiate each individual call by the vessel is to DISTINCT Vessel Name, ATB and Voyage No.

The problem now is that I would want to eliminate vessels that comes in has LOAD TEU or DISC TEU that is less than 50 from the equation.Both side must be above 50 TEU in order to take in the vessel records to find the utilization.

On vessel utilization(help) in which I need codes to factor in this condition. In this table I have already came up with the formula for the formula, preferably not to touch the formula. is there any way I can do this. much appreciated as i have stuck this report for 6 months. Note that I am not able to edit anything from the script. Thanks much!

Re: Difficult conditional expression (aggr?)

sujeetsingh — Tue, 04 Mar 2014 10:03:48 GMT

Where is your attached file

Re: Difficult conditional expression (aggr?)

n1ef5ng1 — Tue, 04 Mar 2014 10:22:23 GMT

Attached file

Re: Difficult conditional expression (aggr?)

n1ef5ng1 — Tue, 04 Mar 2014 10:22:49 GMT

overlooked on that. have attached to it thanks

Re: Difficult conditional expression (aggr?)

ashwanin — Tue, 04 Mar 2014 10:48:15 GMT

you can add Leg to distinguish VVL ,specially for Pivot ports and in script make sum of teus and from there remove the Voyages which has Teus less than 50.

Re: Difficult conditional expression (aggr?)

sujeetsingh — Tue, 04 Mar 2014 11:50:46 GMT

Benn ,

The best thing to do is that you need to derive a flag to filter such records and even group them if required

Re: Difficult conditional expression (aggr?)

sujeetsingh — Tue, 04 Mar 2014 11:56:24 GMT

Benn ,

Actually here you need the filteration in dimension based on the sum() so you need to go for the grouping in script

Re: Difficult conditional expression (aggr?)

n1ef5ng1 — Wed, 05 Mar 2014 01:23:42 GMT

what is leg? can i do it in expression?

Re: Difficult conditional expression (aggr?)

n1ef5ng1 — Wed, 05 Mar 2014 01:24:05 GMT

can i do a flag on expression?i am unable to touch on scripting part