Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
n1ef5ng1
Creator
Creator

<Question> Difficult conditional expression (aggr?)

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!

8 Replies
sujeetsingh
Master III
Master III

Where is your attached file

n1ef5ng1
Creator
Creator
Author

Attached file

n1ef5ng1
Creator
Creator
Author

overlooked on that. have attached to it thanks

ashwanin
Specialist
Specialist

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.

sujeetsingh
Master III
Master III

Benn ,

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

sujeetsingh
Master III
Master III

Benn ,

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

n1ef5ng1
Creator
Creator
Author

what is leg? can i do it in expression?

n1ef5ng1
Creator
Creator
Author

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