topic Syntax in QlikView

Syntax

Mon, 11 Jun 2012 16:32:42 GMT

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;

Syntax

m_woolf — Mon, 11 Jun 2012 17:01:37 GMT

Sum must be used with Group By

Syntax

jagan — Mon, 11 Jun 2012 17:28:22 GMT

Hi,

You need to use Group By, if you use Aggregate functions sum, max etc.

Regards,

Jagan.

Syntax

Mon, 11 Jun 2012 20:47:40 GMT

Okay, so that worked but took forever to complete. How can I speed the function up?

Re: Syntax

jagan — Wed, 13 Jun 2012 08:55:42 GMT

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.