Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Syntax

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;

1 Solution

Accepted Solutions
MVP
MVP

Syntax

Hi,

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

Regards,

Jagan.

4 Replies
mwoolf
Honored Contributor II

Syntax

Sum must be used with Group By

MVP
MVP

Syntax

Hi,

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

Regards,

Jagan.

Not applicable

Syntax

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

MVP
MVP

Re: Syntax

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.

Community Browser