Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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;