topic Re: Replacement of CAST function in QlikView! in QlikView

Replacement of CAST function in QlikView!

Anonymous — Thu, 09 Jun 2011 09:36:06 GMT

What is the replacement of CAST in QlikView. The situation is prior to the QVD file we were using OLEDB/ODBC connection and now we are trying to retrieve data from QVD. but the previous version has cast function in that. this is giving error while reloading. Please let me know about the solution.

Best Regards

Shubham Kumar

Replacement of CAST function in QlikView!

siva_boggarapu — Thu, 09 Jun 2011 09:39:43 GMT

Hello Shubham,

If got the answer for this.Share the solution to me aslo. Thanks advance.

Replacement of CAST function in QlikView!

Anonymous — Thu, 09 Jun 2011 09:41:08 GMT

Dear Mr. Siva,

Sure..however you can have look on this while keep watching the answer for the other qlikview members.

Thanks

Replacement of CAST function in QlikView!

Thu, 09 Jun 2011 22:02:25 GMT

I assume your old ODBC script has something like cast(sales, numeric(8))?

From your qvd you can do:

load num(sales, '#,##0.##') as sales from sales;

to force the number.

other similar functions to consider, month(), year(), money(), date(), time()

post a sample of your script if this doesnt help.

Replacement of CAST function in QlikView!

Anonymous — Fri, 10 Jun 2011 05:24:11 GMT

Thanks Leonard for you answer. here I am posting a sample to make you clear about my situation.

FROM dbo.OverUnder_history with (nolock)
where 1=1
and OU_OutBoundDate >= CAST('$(_FYFromDate_)' AS datetime) and OU_OutBoundDate< CAST('$(_FYToDate_)' AS datetime)
and ((datediff(dd,pull_date, getdate()) between 0 and 😎 or datepart(dw,Pull_date)= 2)
AND (OU_ProductMarketCode IN ('GAUS', 'GACA'))
AND OU_GLName NOT LIKE '%convent%' ;

This was the statement when loading from SQL and now we are loading from QVD so while reloading its gives error. CAST is not recognised function. Even for NOT LIKE Please help.

Regards

Shubham

Replacement of CAST function in QlikView!

Fri, 10 Jun 2011 05:59:52 GMT

Try this, I am assuming that _FYFromDate_ is a variable within Qlikview correct?

FROM dbo.OverUnder_history with (nolock)
where 1=1
and OU_OutBoundDate >= timestamp($(_FYFromDate_),'YYYY-MM-DD hh.mm') and OU_OutBoundDate< timestamp($(_FYToDate_),'YYYY-MM-DD hh.mm')
and ((datediff(dd,pull_date, getdate()) between 0 and 😎 or datepart(dw,Pull_date)= 2)
AND (OU_ProductMarketCode IN ('GAUS', 'GACA'))
AND OU_GLName NOT LIKE '%convent%' ;

Re: Replacement of CAST function in QlikView!

maahivee — Fri, 29 May 2015 19:11:07 GMT

Hi I am having the same issue too.

My sample script is

'AL' + '-' + RTRIM(CAST(ACCOUNT_NUMBER1 AS VARCHAR)) + '-' + RTRIM(CAST(ACCOUNT_NUMBER2 AS VARCHAR)) AS 'Acct Num'

this is when we used to use SQL but now changed to oracle.

And i think if we are using oracle as a database we cannot make any changes to the SQL Select statement, We can only make changes to the preceding load statement. But in the preceding load it is not letting me use the cast function since it is not a designated qlikview function.

Please help me replicate this statement.

Re: Replacement of CAST function in QlikView!

rwunderlich — Fri, 29 May 2015 20:15:57 GMT

Thee is no CAST equivalent in QlikVIew because QlikVIew does not have data types per se. So there should be no need to CAST anything. Just read it the way the database gives it to you.

-Rob

Re: Replacement of CAST function in QlikView!

maxgro — Fri, 29 May 2015 22:35:31 GMT

1) I think you must change the SQL (SQL Server?). Some functions are different from Sql Server to Oracle.

So if you change from Sql Server to Oracle, maybe you get some syntax error (unless your sql is always select * from table). If I'm not wrong, for concat string

+ SQL Server

|| Oracle

RTRIM and CAST should also work in Oracle

2) you can use a simple sql like

sql select ACCOUNT_NUMBER_1, ACCOUNT_NUMBER2 from .....

and change the preceding load using QlikView functions

Re: Replacement of CAST function in QlikView!

petter — Sat, 30 May 2015 02:02:51 GMT

This is obviously Microsoft SQL Sever - which I deduce from the dbo.xxxx and WITH (NOLOCK) and also DATADIFF

SQL Server syntax:

Should be translated to something like this (not tested):

FROM OverUnder_history.QVD (QVD)

WHERE 1=1

AND OU_OutBoundDate >= Num($(_FYFromDate_))

AND OU_OutBoundDate < Num($(_FYToDate_))

AND ( ( Today() - pull_date >= 0 AND Today() - pull_date <= 8 ) OR ( WeekDay( pull_date ) = 2 ) )

AND ( Match( OU_ProductMarketCode , 'GAUS' , 'GACA') > 0 )

AND Not( OU_GLName Like '*convent*');

You can't copy what you had in your original SQL statement and put it at the end of a LOAD statement after FROM ... WHERE. The SQL statement is entirely interpreted by the SQL Database and governed by it's specific SQL-syntax. The LOAD statement is entirely interpreted by QlikView and has some similarities but has it's own syntax and keywords.

Specifically there is no:

dbo.
with (nolock)
CAST( ... AS ... )
DATEDIFF()
DATEPART()
BETWEEN
IN
NOT LIKE
and finally % wildcard

All this has to be translated into something equivalent in QlikView Load Script ... which I did attempt to do above.

Finally if the QVD-file was populated with the SQL-statement you indicated you might not need to do all this logic in the WHERE clause at all... Maybe everything has been filtered correctly so this is superfluous.

Good luck.

Re: Replacement of CAST function in QlikView!

petter — Sat, 30 May 2015 02:09:23 GMT

Didn't even noticed that this was a very old thread ...