topic Re: variable with timestamp in QlikView

variable with timestamp

Anonymous — Thu, 02 Mar 2017 07:20:03 GMT

let lastexectime = '3/1/2017 10:00:00 AM';

load

FROM
[$(DATA_FilePath)$(DATA_FileName)]
(ooxml, embedded labels) where [Modified Date] > '$(lastexectime)' ;

When I used the condition where [Modified Date] > $(lastexectime) without the quotes it throws an error stating garbage value but when I put quotes for the variable it works fine. Can anybody please explain why it works with quotes when $ is already present

Re: variable with timestamp

Anil_Babu_Samineni — Thu, 02 Mar 2017 07:24:54 GMT

that mean, [Modified Date] don't have the Single quote values. That is the reason it shows error

Can you send us the date values for [Modified Date] so that we may know the Behavior of to do this

Re: variable with timestamp

Anonymous — Thu, 02 Mar 2017 07:31:18 GMT

example Modified date = 3/1/2017 2:30:00 PM

I didnt understand by what you wrote above

Re: variable with timestamp

avinashelite — Thu, 02 Mar 2017 07:34:28 GMT

A macro expansion always begins with '$(' and ends with ') ' and the content between brackets defines how the text replacement will be done.

Re: variable with timestamp

Anonymous — Thu, 02 Mar 2017 08:57:25 GMT

Hi Avinash

Can you please throw more light on it? Since I had already defined the variable using let statement and i am already using $ expansion why is there a need to use single quotes?

Re: variable with timestamp

Miguel_Angel_Baeyens — Thu, 02 Mar 2017 09:12:22 GMT

Quotes are needed here. Irrespective how the date has been defined in the script, either using LET or SET, what the WHERE expects in the query is a string, not a number, therefore the need for the quotes to specify a literal.

Specifically with dates, stored values do not look like 02/03/2017 rather than 42976, so for QlikView to understand that value you need

WHERE DateField = '02/03/2017' // even if you are not using a variable.

Re: variable with timestamp

cwolf — Thu, 02 Mar 2017 09:18:33 GMT

Hi Sanjyot,

a text variable needs to use it with single quotes,

a number variable needs to use it without single quotes:

Text variable:

set lastexectime = '3/1/2017 10:00:00 AM';

load

FROM

[$(DATA_FilePath)$(DATA_FileName)]

(ooxml, embedded labels) where [Modified Date] > '$(lastexectime)' ;

Number variable:

let lastexectime = num(Date('3/1/2017 10:00:00 AM'));

load

FROM

[$(DATA_FilePath)$(DATA_FileName)]

(ooxml, embedded labels) where [Modified Date] > $(lastexectime);

Re: variable with timestamp

Anonymous — Thu, 02 Mar 2017 09:23:34 GMT

Thanks this explains it very well.

I have used the below but now it doesnt seem to work.

let lastexectime = date(ReloadTime(),'MM/DD/YYYY hh:mm:ss TT');

load

FROM
[$(DATA_FilePath)$(DATA_FileName)]
(ooxml, embedded labels) where [Modified Date] > '$(lastexectime)' ;

Re: variable with timestamp

Miguel_Angel_Baeyens — Thu, 02 Mar 2017 09:27:59 GMT

How exactly the field [Modified Date] looks like in your QVD? Maybe it is not storing the time part or has a different format than MM/DD/YYYY when you display it on a listbox.

Re: variable with timestamp

Anonymous — Thu, 02 Mar 2017 09:32:47 GMT

I used the below with the number variable.. but it shows garbage as error

let lastexectime = date(ReloadTime(),'MM/DD/YYYY hh:mm:ss TT');

load

FROM
[$(DATA_FilePath)$(DATA_FileName)]
(ooxml, embedded labels) where [Modified Date] > $(lastexectime) ;

Re: variable with timestamp

cwolf — Thu, 02 Mar 2017 09:35:02 GMT

to get it as number:

let lastexectime = num(date(ReloadTime()));

Re: variable with timestamp

Anonymous — Thu, 02 Mar 2017 09:37:43 GMT

Modifed date saves value as 3/1/2017 10:45:00 AM

Re: variable with timestamp

Miguel_Angel_Baeyens — Thu, 02 Mar 2017 09:41:23 GMT

Are there two spaces there between the date part and the time part or is that me?

Re: variable with timestamp

Anonymous — Thu, 02 Mar 2017 09:46:55 GMT

Thanks..it worked..I was missing out the num function while comparing it..

Re: variable with timestamp

Anonymous — Thu, 02 Mar 2017 09:48:29 GMT

Christian provided a solution that worked ..

He suggested to use let lastexectime = num(date(ReloadTime())); and keep the rest of the script as it is.

Thanks for your help..Explanation provided earlier with quotes has been very helpful indeed.

Re: variable with timestamp

Miguel_Angel_Baeyens — Thu, 02 Mar 2017 09:55:29 GMT

Right, because you are using the LET function. You can also use the SET if you are hardcoding the date instead of using the function.

Good luck!