Missing ')' - can't see it

[#1]

Hi All,

I've been trying to create a new table using a LOAD statement on a table that already exists. Qlikview is telling me there is a missing ')' somewhere in my script, but for the life of me I can't see where. Script is below:

Error in expression:

')' expected

add REVENUESUMMARY:

load

  monthname(SERVICEDATE) as SERVICEMONTHYEAR,

  year(SERVICEDATE) as SERVICEYEAR,

  month(SERVICEDATE) as SERVICEMONTH,

  sum(SALESREVENUEEUR) as REVENUE,

  PRODUCTCODE as PRODUCTCODE

RESIDENT BOOKINGDETAIL_AND_SUPPLIERDETAIL

where year(SERVICEDATE) >= year(current date-1 year)

Any ideas?

Thanks,

Rory.

[preminqlik]

hi rory,

try this

load

  monthname(SERVICEDATE) as SERVICEMONTHYEAR,

  year(SERVICEDATE) as SERVICEYEAR,

  month(SERVICEDATE) as SERVICEMONTH,

  sum(SALESREVENUEEUR) as REVENUE,

  PRODUCTCODE as PRODUCTCODE

RESIDENT BOOKINGDETAIL_AND_SUPPLIERDETAIL

where year(SERVICEDATE) >= year(now())-1;

[CELAMBARASAN · Accepted solution]

2 errors in your script

1. is current data - 1 year

2. Sum cannot be used without group by

so you could use something like this

load

  monthname(Max(SERVICEDATE)) as SERVICEMONTHYEAR,

  year(Max(SERVICEDATE)) as SERVICEYEAR,

  month(SERVICEDATE) as SERVICEMONTH,

  sum(SALESREVENUEEUR) as REVENUE,

  PRODUCTCODE as PRODUCTCODE

RESIDENT BOOKINGDETAIL_AND_SUPPLIERDETAIL

where year(SERVICEDATE) >= (year(Today()) - 1)

Group by month(SERVICEDATE), PRODUCTCODE;