Delete records if it does not fulfill condition. *Sample file included

[n1ef5ng1]

Hi gurus,

I have this table with Month_Year, Plane_Name, Service_Type_Indicator, Discharge_Cargo, Load_Cargo, Total_Cargo, TXIT_Cargo and Capacity.

For this report, I would want to check how utilize the plane is by service type indicator.

Service Type indicator is the route the plane takes

Total_Cargo is Discharge Cargo + Load Cargo

TXIT_Cargo is how many cargo it stays on the plane without discharging and loading

Capacity is how many cargo the plane is able to handle.

Another table consist of Activity_Code where it will check Load or Discharge Cargo.

And the Total_Cargo where it shows the cargo it discharge by using the activity_code

To check how utilize the plane is, the formula is.

=(

sum({<Service_Type_Indicator = {'EFAR'}>}Total_Cargo) + (sum({<Service_Type_Indicator={'EFAR'}>}TXIT_Cargo) * 2))
/
(Sum(aggr(if(Service_Type_Indicator='EFAR',
(Capacity)),Service_Type_Indicator,Month_Year,Plane_Name)) * 2)
* 100

In short , (TOTAL CARGO + (TOTAL TXIT CARGO * 2))/ Capacity * 2

Please look at the sample file for better clarity.

Now my problem is, of the 8 plane name as shown on the sample file. I would want to remove plane that has Discharge Cargo OR Load Cargo that is lower than 50.

Meaning plane name Boeing, Zoom and Vean should not be included in the expression as it will disrupt the %. (Business context).

if(Activity_Code = 'LOAD' and TOTAL_cargo > 50) (This logic)

Is there anyway I can do this to add on to my expression code? My expression right now applies the formula but it includes the eight planes..

Thanks very much for spending the time to read and understand. appreciate it.

Message was edited by: Bennn Bennn Add in activity_Code

[n1ef5ng1]

anybody

[christian77]

If you want to remove records from QlikView application, you need a macro.

1st Get the Appi Guide 11

2nd Go to Automation Example ant type Update. Get the module and modify to your needs.

3rd Go to Settings > Server and check the box Enable Dynamic Data Update (otherwise it won't work)

Voila.

Other way could be to LOAD your table once and then reload again from resident taking only those records fulfilling the condition.

Then erase the first table with Drop Table statement.

[n1ef5ng1]

i am unable to use macro.

however, deleting records only applies to this particular table. for other table, there is a need to have these records.

anybody?