Unrecognised function 'ceil'

[#1]

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

[sunny_talwar]

Can you post your script master calendar script here (assuming that's where you are creating Purchase_Quarter)?

[#3]

Hi,

Use Ciel(num(month(Date))/3) as Purchase_Quarter and check

[sunny_talwar]

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.

[#5]

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;

[sunny_talwar]

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

[sunny_talwar]

Misspelled comment was not for you Madhura, it was for Harshitha.

[qlikmsg4u]

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;

[sasiparupudi1 · Accepted solution]

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

[sunny_talwar]

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;