Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
pauledrich
Creator
Creator

Script error

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.

5 Replies
anandathome
Creator
Creator

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
Creator
Creator
Author

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

Not applicable

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
Partner
Partner

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
MVP
MVP

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.