topic Re: Script error in QlikView

Script error

pauledrich — Thu, 08 Aug 2013 11:53:00 GMT

Hi All,

New to QV here and learning quickely, however, I have inherited a project which has a a tab within the script which has the following code and fails; any ideas whats wrong?

The Field names are correct and the tab is titled "optimise for Calc", other tabs are "Main" & "QVD Load" both work fine when stepped through debug..

Load

Month as Monthall,

RegionName as Region,

if(isnull(CancelledDate)and RegionName<>'Unallocated' and TransactionType='C',sum(PricePaid)/sum(Weight)) as OverallPPG,

if(isnull(CancelledDate)and RegionName<>'Unallocated' and TransactionType='C',sum(Weight)) as TotalVolume,

if(isnull(CancelledDate)and RegionName<>'Unallocated' and TransactionType='C',avg(ActSpotPrice)) as TotalSpotPrice

Resident OverallTable

group by Month, RegionName,TransactionType,CancelledDate;

Thanks In advance.

Re: Script error

anandathome — Thu, 08 Aug 2013 11:58:46 GMT

Hi Paul,

Could you give a screenshot of the error you encountered?

And it looks that you are using aggregation function without

a "Group By" clause.

-Anand

Re: Script error

pauledrich — Thu, 08 Aug 2013 12:19:54 GMT

Hi, When running the debug the pop up window is:-

Table not found

Load Month as Monthall,

RegionName as Region,

if(isnull(CancelledDate)and RegionName<>'Unallocated' and TransactionType='C',sum(PricePaid)/sum(Weight)) as OverallPPG,

if(isnull(CancelledDate)and RegionName<>'Unallocated' and TransactionType='C',sum(Weight)) as TotalVolume,

if(isnull(CancelledDate)and RegionName<>'Unallocated' and TransactionType='C',avg(ActSpotPrice)) as TotalSpotPrice

Resident OverallTable

group by Month, RegionName,TransactionType,CancelledDate

Re: Script error

Thu, 08 Aug 2013 12:26:40 GMT

check this table OverallTable, i think you drop this table.

and then remove group by in your code, because group by work with aggrigate function.

Re: Script error

mseeck75 — Thu, 08 Aug 2013 12:28:58 GMT

Hi,

it seems that the problem are the if - clauses. You can try it as follows:

Load

Month as Monthall,

RegionName as Region,

sum(if(isnull(CancelledDate)and RegionName<>'Unallocated' and TransactionType='C',PricePaid/Weight,0)

as OverallPPG,

sum(if(isnull(CancelledDate)and RegionName<>'Unallocated' and TransactionType='C',Weight,0) as TotalVolume,

avg(if(isnull(CancelledDate)and RegionName<>'Unallocated' and TransactionType='C',ActSpotPrice,0) as TotalSpotPrice

Resident OverallTable

group by Month, RegionName,TransactionType,CancelledDate;

Matthias

Re: Script error

tresB — Thu, 08 Aug 2013 12:41:53 GMT

Paul,

it seesms that 'OverallTable' table is not there in the script prior to this section of code. And there is no need to remove 'Group By', else your AVG() (an aggregation function) would not work.