Hello,
Hope someone can help.
I'm currently loading data from a sql database which contains 2 datetime fields 'StartDateTime' and 'EndDateTime'
the data in these fields, once in qlikview looks like this 2009-09-08 11:13:51.000
During my load i am creating a new field in my Qlikview table called 'Duration' by simply performing the following
...
EndDateTime - StartDateTime as Duration
...
this works fine, giving me a numeric value e.g. 0.209753484
Now heres where my problem starts
I need to trim dowm the records that i am selecting based upon their contents, i can't do this with a simple select statement so am using the
peek(field, row, table) function to look at the data in an initial table that i have loaded and then storing the values from the rows i want in variables, the date time values go into
$(StartVar) and $(EndVar)
I then use an inline load with these variables to create my neat and tidy table of the rows i am after
LOAD INLINE [StartDateTime, EndDateTime
$(StartVar), $(EndVar)]
This seems to work fine leaving me with data in my fields StartDateTime and EndDateTime that still looks like 2009-09-08 11:13:51.000
HOWEVER
When i now try and calculate the duration in the load using
...
EndDateTime - StartDateTime as Duration
...
I don't get any errors BUT the duration value i receieve, for EVERY row is '-'
I suspect this may be something to do with Qlikview thinking the date 2009-09-08 11:13:51.000 is a text string.. or something like that..
Can anybody advise? I've been playing around with this all day. Well and truly stuck.
Thanks - Matt
