Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am writing a script like: ceil(month(Date)/3) as Purchase_Quarter and getting an error as 'unrecognised built in function 'ceil''.
Please help.
Thanks,
Madhura
if you are using sql server, it should be CEILING. Ceil is a qv function
Purchase_Order:
SQL SELECT Date as Purchase_Order_Date,
"Gross Amount" as Purchase_Gross_amount,
"Purchase ID",
"Purchase Order #",
Tax as Purchase_Tax,
year(Date) as Purchase_Year,
month(Date) as Purchase_month,
'Q' & CEILING(month(Date)/3) as Purchase_Quarter
FROM XtreamDB.dbo."Purchase Order";
store Purchase_Order into Z:\Madhura\QlikviewTraining\Sales_Forecast\Purchase_Order.qvd;
hth
Sasi
Can you post your script master calendar script here (assuming that's where you are creating Purchase_Quarter)?
Hi,
Use Ciel(num(month(Date))/3) as Purchase_Quarter and check
Use Ciel(num(month(Date))/3) as Purchase_Quarter and check
I think you have misspelled Ceil and we don't really need Num, because month function is dual in nature.
Hi Sunny,
No, I havent mispeled it. I checked twice for that sake. My script is like this:
Purchase_Order:
SQL SELECT Date as Purchase_Order_Date,
"Gross Amount" as Purchase_Gross_amount,
"Purchase ID",
"Purchase Order #",
Tax as Purchase_Tax,
year(Date) as Purchase_Year,
month(Date) as Purchase_month,
'Q' & Ceil(month(Date)/3) as Purchase_Quarter
FROM XtreamDB.dbo."Purchase Order";
store Purchase_Order into Z:\Madhura\QlikviewTraining\Sales_Forecast\Purchase_Order.qvd;
You are using Ceil in SQL, I am not sure if SQL uses Ceil or other function. Try this:
Purchase_Order:
LOAD *,
'Q' & Ceil(month(Purchase_Order_Date)/3) as Purchase_Quarter;
SQL SELECT Date as Purchase_Order_Date,
"Gross Amount" as Purchase_Gross_amount,
"Purchase ID",
"Purchase Order #",
Tax as Purchase_Tax,
year(Date) as Purchase_Year,
month(Date) as Purchase_month,
FROM XtreamDB.dbo."Purchase Order";
store Purchase_Order into Z:\Madhura\QlikviewTraining\Sales_Forecast\Purchase_Order.qvd;
Update: Forgot to add a Semi-Colon after 'Q' & Ceil(month(Purchase_Order_Date)/3) as Purchase_Quarter. Added it now
Misspelled comment was not for you Madhura, it was for Harshitha.
Ceil is qlikview function,
use preceding load like sunindia told
Purchase_Order:
LOAD *,
'Q' & Ceil(month(Purchase_Order_Date)/3) as Purchase_Quarter
SQL SELECT Date as Purchase_Order_Date,
"Gross Amount" as Purchase_Gross_amount,
"Purchase ID",
"Purchase Order #",
Tax as Purchase_Tax,
year(Date) as Purchase_Year,
month(Date) as Purchase_month,
FROM XtreamDB.dbo."Purchase Order";
store Purchase_Order into Z:\Madhura\QlikviewTraining\Sales_Forecast\Purchase_Order.qvd;
if you are using sql server, it should be CEILING. Ceil is a qv function
Purchase_Order:
SQL SELECT Date as Purchase_Order_Date,
"Gross Amount" as Purchase_Gross_amount,
"Purchase ID",
"Purchase Order #",
Tax as Purchase_Tax,
year(Date) as Purchase_Year,
month(Date) as Purchase_month,
'Q' & CEILING(month(Date)/3) as Purchase_Quarter
FROM XtreamDB.dbo."Purchase Order";
store Purchase_Order into Z:\Madhura\QlikviewTraining\Sales_Forecast\Purchase_Order.qvd;
hth
Sasi
Use this: Missed a semi-colon in my earlier post:
Purchase_Order:
LOAD *,
'Q' & Ceil(month(Purchase_Order_Date)/3) as Purchase_Quarter;
SQL SELECT Date as Purchase_Order_Date,
"Gross Amount" as Purchase_Gross_amount,
"Purchase ID",
"Purchase Order #",
Tax as Purchase_Tax,
year(Date) as Purchase_Year,
month(Date) as Purchase_month,
FROM XtreamDB.dbo."Purchase Order";
store Purchase_Order into Z:\Madhura\QlikviewTraining\Sales_Forecast\Purchase_Order.qvd;