Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

QV does not recogzine timestamp from other system


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

4 Replies
Highlighted

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

Highlighted
Not applicable

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

Highlighted

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

Highlighted
Not applicable

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