
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Replacement of CAST function in QlikView!
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
- Tags:
- new_to_qlikview
- « Previous Replies
-
- 1
- 2
- Next Replies »


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello Shubham,
If got the answer for this.Share the solution to me aslo. Thanks advance.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dear Mr. Siva,
Sure..however you can have look on this while keep watching the answer for the other qlikview members.
Thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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%' ;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
or
2) you can use a simple sql like
sql select ACCOUNT_NUMBER_1, ACCOUNT_NUMBER2 from .....
and change the preceding load using QlikView functions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This is obviously Microsoft SQL Sever - which I deduce from the dbo.xxxx and WITH (NOLOCK) and also DATADIFF
SQL Server syntax:
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%' ;
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.

- « Previous Replies
-
- 1
- 2
- Next Replies »