topic Re: Unrecognised function 'ceil' in QlikView

Unrecognised function 'ceil'

Fri, 04 Sep 2015 05:29:13 GMT

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

Re: Unrecognised function 'ceil'

sunny_talwar — Fri, 04 Sep 2015 05:32:59 GMT

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

Re: Unrecognised function 'ceil'

Fri, 04 Sep 2015 05:36:30 GMT

Hi,

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

Re: Unrecognised function 'ceil'

sunny_talwar — Fri, 04 Sep 2015 05:38:45 GMT

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.

Re: Unrecognised function 'ceil'

Fri, 04 Sep 2015 05:44:00 GMT

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;

Re: Unrecognised function 'ceil'

sunny_talwar — Fri, 04 Sep 2015 05:45:53 GMT

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

Re: Unrecognised function 'ceil'

sunny_talwar — Fri, 04 Sep 2015 05:46:51 GMT

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

Re: Unrecognised function 'ceil'

qlikmsg4u — Fri, 04 Sep 2015 05:50:30 GMT

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;

Re: Unrecognised function 'ceil'

sasiparupudi1 — Fri, 04 Sep 2015 05:51:58 GMT

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

Re: Unrecognised function 'ceil'

sunny_talwar — Fri, 04 Sep 2015 05:52:03 GMT

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;

Re: Unrecognised function 'ceil'

Fri, 04 Sep 2015 05:53:00 GMT

Sunny, you were right, may be SQL does not recognise ceil function. But, if I load the quarter seperately like you showed, it does not select the other fields from SQl select statement.

Re: Unrecognised function 'ceil'

sunny_talwar — Fri, 04 Sep 2015 05:54:49 GMT

When you use * (asterisk) in your preceding load you get all your fields from the below table.

Re: Unrecognised function 'ceil'

sunny_talwar — Fri, 04 Sep 2015 05:55:42 GMT

Or use CEILING as proposed by Sasidhar Parupudi

Re: Unrecognised function 'ceil'

Fri, 04 Sep 2015 05:56:04 GMT

Thanks Sasidhar, that worked as I wanted.