Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

variable with timestamp

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

1 Solution

Accepted Solutions
cwolf
Creator III
Creator III

to get it as number:

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

View solution in original post

15 Replies
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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Anonymous
Not applicable
Author

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
Not applicable
Author

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
Creator III
Creator III

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
Not applicable
Author

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
Not applicable
Author

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