QV does not recogzine timestamp from other system

[#1]

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

[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

[#3]

Hi Peter,

Unfortunately not, although your programming script looks good.

When I have reloaded the amended script and subsequently made a Line Chart, then the X-axis is not recognized as a timestamp format. I got an error "No nummeric value on X-axis'.

Any idea why?

Thanks,

Best regards,

Cornelis

[Peter_Cammaert]

The X-Axis of a Line Chart doesn't need to be numerical. Something else is wrong here.

How is your ListBox (ts) behaving? Any improvement there?

Any chance you can post an example QVW document with some data to investigate?

Peter

[#5]

Hi Peter,

I have already solved it. The problem can be ascribed to an extra blank string in the timestamp format from the database system:

01/09/12 00:00:00.0 versus

01/09/12  00:00:00.0

It is in QV hardly visible (you need a to have a very very closely look) and the correct format is

date#(ts, 'DD/MM/YYYY hh:mm:ss.f') should be:

date#(ts, 'DD/MM/YYYY  hh:mm:ss.f')

Thank you for you help, it has helped me a lot!

Best regards,

Cornelis