Script error

[pauledrich]

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.

[anandathome]

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

[pauledrich]

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

[#4]

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.

[mseeck75]

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;

BR

Matthias

[tresesco]

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.