1 Reply Latest reply: Sep 12, 2014 6:29 AM by Igor Stepanov RSS

    Direct discovery - time data type. PROBLEM!

    Igor Stepanov

      Hello,

       

      My problem - Direct discovery and time data type

      Please help.

       

      Source MS Sql Server 2008 R2

       

      Table UsersLog whith field lgdate - data type "Datetime"

       

      connect oledb

       

      DIRECT QUERY

      DIMENSION

        userLogin as "Login"

      DETACH

        NATIVE('CONVERT(varchar(10), lgdate,102)') as "Date",

        NATIVE('rtrim(ltrim(str(datepart(hh, lgdate))))+char(45)+rtrim(ltrim(str( datepart(mi, lgdate))))+char(45)+rtrim(ltrim(str(datepart(s, lgdate))))') as "Time 1",

        NATIVE('replace(CONVERT(varchar(8), lgdate,108),'':'',''|'')') as "Time 2",

        NATIVE('CONVERT(varchar(19), lgdate)') as "DateTime 1",

        NATIVE('CONVERT(varchar(19),lgdate,120)') as "DateTime 2",

        lgdate

      from UsersLog

       

      Result after LOAD:

      DateDateTime 1DateTime 2lgdateLoginTime 1Time 2
      2014.09.04Sep  4 2014 12:25PM2014-09-04 12:25:4204.09.2014 12:25zzz12-25-4212|25|42
      2014.09.04Sep  4 2014 12:47PM2014-09-04 12:47:0004.09.2014 12:47xxx12-47-012|47|00
      2014.09.04Sep  4 2014 12:53PM2014-09-04 12:53:1904.09.2014 12:53ccc12-53-1912|53|19

       

      All good!!!

       

      Next, add new record in source table and result QV:

      DateDateTime 1DateTime 2lgdateLoginTime 1Time 2
      2014.09.04Sep  4 2014 12:25PM2014-09-04 12:25:4204.09.2014 12:25zzz12-25-4212|25|42
      2014.09.04Sep  4 2014 12:47PM2014-09-04 12:47:0004.09.2014 12:47xxx12-47-012|47|00
      2014.09.04Sep  4 2014 12:53PM2014-09-04 12:53:1904.09.2014 12:53ccc12-53-1912|53|19
      2014.09.04 vvv

      Where is Time new record? what is problem?

       

      Thanks

        • Re: Direct discovery - time data type. PROBLEM!
          Igor Stepanov

          QlikView Direct Discovery FAQ 11.2 SR7.pdf

           

          • Can be used to build aggregated charts on homogeneous large data sets

          • Can be used to look at detail records in a table box on large data sets

          Can reflect updated records without reloads (not new records)

          • Can support more than one Direct Discovery table in certain scenarios

          • Not as fast as in memory apps

          • Will always be slower compared to SQL query run times on source due to associative model

          calculation times

          • Not a solution for scalability/performance issues in the underlying source

          • Not designed to convert all tables in apps into Direct Discovery mode

          • Not a real time solution