Discussion Board for collaboration on QlikView Scripting.
Whats wrong with my syntax? When I create the first table on its own, it runs fine, but when I try and add the if statement it fails. The debugger is not helpful.
In_Averagebps as In_Averagebps1,
(In_Averagebps/1000) AS In_Averagebps,
(Out_Averagebps/1000) AS Out_Averagebps,
ApplyMap('Map_InterfacesIn',NodeID&'-'&InterfaceID) AS InBandwidth,
ApplyMap('Map_InterfacesOut',NodeID&'-'&InterfaceID) AS OutBandwidth,
applymap('Map_InterfaceName',InterfaceID) AS InterfaceName,
applymap('Map_BandwidthandCircuit',NodeID) AS BandwidthandCircuit
IF(sum(InBandwidth)=0,sum(OutBandwidth/OutBandwidth) * 100,
IF(sum(OutBandwidth),(sum(In_Averagebps)/sum(InBandwidth)) * 100,
sum(Out_Averagebps/OutBandwidth)+sum(In_Averagebps/InBandwidth)*50))) AS AVERAGE_of_CircuitUtil_AvgRecvXmit2
DROP Table InterfaceTrafficDetailTemp1;
Go to Solution.
You need to use Group By, if you use Aggregate functions sum, max etc.
Sum must be used with Group By
Okay, so that worked but took forever to complete. How can I speed the function up?
Try this, this may faster a bit because we are calculating values once and reusing in If.
IF((InBandWidthTotal +OutBandwidthTotal) =0,0,
IF(InBandWidthTotal=0,sum(OutBandwidth/OutBandwidth) * 100,
IF(OutBandwidthTotal, (In_AveragebpsTotal/InBandWidthTotal) * 100,
sum(Out_Averagebps/OutBandwidth)+sum(In_Averagebps/InBandwidth)*50))) AS AVERAGE_of_CircuitUtil_AvgRecvXmit2;
sum(InBandwidth) AS InBandWidthTotal,
sum(OutBandwidth) AS OutBandwidthTotal,
sum(In_Averagebps) AS In_AveragebpsTotal
Group by *;