variable with timestamp

[Anonymous]

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

[Anil_Babu_Samineni]

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

[Anonymous]

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

I didnt understand by what you wrote above

[avinashelite]

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

[Anonymous]

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?

[Miguel_Angel_Baeyens]

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.

[cwolf]

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

[Anonymous]

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

[Miguel_Angel_Baeyens]

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.

[Anonymous]

Hi

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