Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
InterfaceTrafficDetailTemp:
LOAD InterfaceID,
NodeID,
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
FROM InterfaceTrafficDetail2.qvd(qvd);
InterfaceTraffic1:
Load
*, IF(sum(InBandwidth)+sum(OutBandwidth)=0,0,
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
RESIDENT InterfaceTrafficDetailTemp;
DROP Table InterfaceTrafficDetailTemp1;
Hi,
You need to use Group By, if you use Aggregate functions sum, max etc.
Regards,
Jagan.
Sum must be used with Group By
Hi,
You need to use Group By, if you use Aggregate functions sum, max etc.
Regards,
Jagan.
Okay, so that worked but took forever to complete. How can I speed the function up?
Hi Alex,
Try this, this may faster a bit because we are calculating values once and reusing in If.
InterfaceTraffic1:
Load
*,
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;
LOAD
*,
sum(InBandwidth) AS InBandWidthTotal,
sum(OutBandwidth) AS OutBandwidthTotal,
sum(In_Averagebps) AS In_AveragebpsTotal
RESIDENT InterfaceTrafficDetailTemp
Group by *;
Regards,
Jagan.