Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

8 Replies
Gysbert_Wassenaar

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.


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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.

sample2.png

Gysbert_Wassenaar

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(...) ?


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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

swuehl
MVP
MVP

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()
                      )
            )
;

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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