topic Re: Timestamp in load script, the time part is not what is should be. in QlikView

Timestamp in load script, the time part is not what is should be.

Anonymous — Thu, 01 Nov 2012 10:52:29 GMT

I have a variable in the loading script containing a timestamp (vLastLoadDateTime).

I create a numeric for this, to use in the LOAD statement (vLastLoadDateTimeNUM).

When using it in the LOAD statement the time part of the timestamp is changed to 00:00:00 strange enough in which I do not get the correct results.

See part of the script below:

...
LET vLastLoadDateTime = timestamp(vStartLOAD); // voorgaande LOAD start!!
LET vLastLoadDateTimeNUM = num(vLastLoadDateTime); // voorgaande LOAD start!!
...
// WHERE CLAUSE
Serviceorders:
LOAD *
          ,timestamp([Serviceorders.Orderdatum]          +  [Serviceorders.Ordertijd]) as [Serviceorders.Orderdatumtijd]
          ,timestamp([Serviceorders.Einddatum]          +  [Serviceorders.Eindtijd]) as [Serviceorders.Einddatumtijd] 
          ,timestamp($(vLastLoadDateTimeNUM))as TEST
//          ,num($(vLastLoadDateTime) )as TEST2 
RESIDENT Serviceorders_temp
WHERE if(Serviceorders.EventType = 1, num(timestamp([Serviceorders.Orderdatum]          +  [Serviceorders.Ordertijd])) > num($(vLastLoadDateTimeNUM)),
            if(Serviceorders.EventType = 3, num(timestamp([Serviceorders.Einddatum]          +  [Serviceorders.Eindtijd])) > num($(vLastLoadDateTimeNUM)) 
                                                                                            and
                                                                                              num(timestamp([Serviceorders.Einddatum]          +  [Serviceorders.Eindtijd])) <= num(now())
                      )
            )
;

e.g.

When I show after loading the vLastLoadDateTimeNUM as timestamp it shows:

1-11-2012 11:15:12

But when looking in the LOAD part the vLastLoadDateTimeNUM shows:

1-11-2012 00:00:00

Anyone has a clue what is happening here?

thx in advance!!

Anita

Re: Timestamp in load script, the time part is not what is should be.

Gysbert_Wassenaar — Thu, 01 Nov 2012 11:07:06 GMT

You don't specify a format for num, so the format set in the operating system is used. In your case that seem to mean it discards the fraction. So you end up with a date without the time fraction.

Re: Timestamp in load script, the time part is not what is should be.

Anonymous — Thu, 01 Nov 2012 11:12:13 GMT

But why is the variable itself containing the fraction part?? When showing this variable in the front side using timestamp it shows the correct value.

Any idea how to solve this?? When I use the timestamp vLastLoadDateTime it gives errors.

Re: Timestamp in load script, the time part is not what is should be.

Gysbert_Wassenaar — Thu, 01 Nov 2012 11:52:42 GMT

I guess I'm going blind. I could have sworn that I read this:

When I show after loading the vLastLoadDateTime as timestamp it shows:

1-11-2012 11:15:12

But when looking in the LOAD part the vLastLoadDateTimeNUM shows:

1-11-2012 00:00:00

If you wrote vLastLoadDateTimeNUM in both lines, then I'm obviously wrong.

Anyway, the error... Aren't you missing an if somewhere in the where clause?

if(Serviceorders.EventType = 1,

num(timestamp([Serviceorders.Orderdatum] + [Serviceorders.Ordertijd])

) <-- end of if statement

> num($(vLastLoadDateTimeNUM)), <-- what is compared to num(...) ?

Re: Timestamp in load script, the time part is not what is should be.

Anonymous — Thu, 01 Nov 2012 12:06:31 GMT

Maybe I really didn't explain it that well . . . it is about the vLastLoadDateTimeNUM which gives in the scripting side (loaded in the table as TEST) 1-11-2012 00:00:00 . . .

In the frontside i showed it in a textbox as a timestamp and it displays: 1-11-2012 11:15:12

The statement itself is correct . . . think you miscounted the '(' and ')' . . . you said

if(Serviceorders.EventType = 1,
num(timestamp([Serviceorders.Orderdatum] + [Serviceorders.Ordertijd])
) <-- end of if statement
> num($(vLastLoadDateTimeNUM)), <-- what is compared to num(...) ?

but the <-- end of if statement you say is not the end of the if, but the end bracket of the num function ... 😉

the story is that the num vLastLoadDateTimeNUM gives 2 different values.

on scripting side: 1-11-2012 00:00:00

on front side: 1-11-2012 11:15:12

I really need the front side value at the scripting side . . . but can't see what is going wrong and why the time is set to 00:00:00 when using it in the load statement.

thx

Re: Timestamp in load script, the time part is not what is should be.

swuehl — Thu, 01 Nov 2012 13:20:56 GMT

In your where clause, you just need to compare numericals in the if() statement, so no need to format the numbers uisng num().

Would be interesting to know how you defined vStartLOAD, but even without knowing, I believe your issue arise from how QV expands the dollar signe expansion.

As you see, timestamp($(vLastLoadDateTimeNUM)) expands to timestamp(41214,50...).

You are probably using a decimal separator ','. But QVs internal decimal separator is '.' so this number will not be correctly interpreted, instead it will be interpreted at best as two arguments to function timestamp.

Look into how QV handles dollar sign expansion and how you let QV return a legal decimal-point reflection using $(#vVariable).

Depending on how vStartLOAD is defined, you can also try

WHERE if(Serviceorders.EventType = 1, ([Serviceorders.Orderdatum] + [Serviceorders.Ordertijd]) > $(vLastLOAD), if(Serviceorders.EventType = 3, ([Serviceorders.Einddatum] + [Serviceorders.Eindtijd]) > $(vLastLOAD)

and ([Serviceorders.Einddatum] + [Serviceorders.Eindtijd]) <= now() ) ) ;

Re: Timestamp in load script, the time part is not what is should be.

Anonymous — Thu, 01 Nov 2012 13:30:44 GMT

the vStartLOAD is set after retrieving the value for vLastLoadDateTime(Num) ... so actually the load time from the last load ...

let vStartLOAD = now()

Sounds like a really good explanation the separator difference . . . but also a bit dangerous since it is not that obviousliy visible something goes wrong.

thx . . . will try some things out

Re: Timestamp in load script, the time part is not what is should be.

Anonymous — Thu, 01 Nov 2012 13:40:44 GMT

dit a test by changing the variable:

SET DecimalSep='.'; // was ','

this worked!! but now i'm looking for a way for changing this for the better . . . because the dutch writing is with a ',' ...

thx

Re: Timestamp in load script, the time part is not what is should be.

Anonymous — Thu, 01 Nov 2012 13:54:10 GMT

I changed the DecimalSep just before and after the LastLoadDateTimeNum ... this did the trick ... 😉