10 Replies Latest reply: May 29, 2015 10:09 PM by Petter Skjolden RSS

    Replacement of CAST function in QlikView!

    Shubham Kumar

      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!

          Hello Shubham,

           

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

          • Replacement of CAST function in QlikView!
            Leonard Short

            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!
                Shubham Kumar

                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 8) 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!
                    Leonard Short

                    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 8) 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!
                        mahathi vucha

                        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!
                            Rob Wunderlich

                            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!
                              Massimo Grossi

                              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

                               

                          • Re: Replacement of CAST function in QlikView!
                            Petter Skjolden

                            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 8) 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.