4 Replies Latest reply: Oct 21, 2013 1:48 AM by Cock Verboom RSS

    QV does not recogzine timestamp from other system

    Cock Verboom


      Dear all,

       

      I use this script in the Sript editor to import data from an external database system

       

       

      CONNECT TO W0Process;

      tableOne:
      select name,ts ,VALUE AS "PRESSURE-AC9" WIDTH 10 from history(80) where (period=600) and (name='PRESSURE_AC9')
      and (request=2) and ts between '01/04/12 00:00:00.0' and '02/04/12 23:59:00.0';

       

      The list box shows records like '01/04/12 00:00:00.0' but QV regards this not a timestamp value but a non-nummeric value.

      Ik have already tried to convert format using List box properties >> Number but it seems to allow only

      D-M-YYYY h:mm:ss[.fff]  format.

       

      Any idea how to convert into nummeric?

       

      Looking forward...

      Best regards,

       

      Cornelis

       

        • Re: QV does not recogzine timestamp from other system
          Peter Cammaert

          I guess that the timestamps reside in field ts, correct?

          Then add a preceding load to your SELECT like this:

           

          CONNECT TO W0Process;

           

          tableOne:

          LOAD name, date#(ts, 'DD/MM/YYYY hh:mm:ss.f') as ts, [PRESSURE-AC9];

          select name,ts ,VALUE AS "PRESSURE-AC9" WIDTH 10 from history(80) where (period=600) and (name='PRESSURE_AC9')

          and (request=2) and ts between '01/04/12 00:00:00.0' and '02/04/12 23:59:00.0';

           

          The QlikView (i.e. non-SQL) function date#() forces interpretation of values as dates according to a format string. Should work.

           

          Good luck,

           

          Peter