Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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
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

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

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

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