
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
String Date YYYYMMDD > Epoch date comparison
Hello my QV friends. I have been stuck on a problem for a couple of days now and just cannot figure out how to resolve the issue. I am going to try to be as specific as possible.
I have created a variable named vTwoWeeksAgo. It calculates today's date minus 14 days.
SET vTwoWeeksAgo='';
LET vTwoWeeksAgo=Date(Today()-14);
I then use a LOAD statement that has the following field:
LOAD
DATE(DATE#(PDDCR, 'YYYYMMDD'), 'MM/DD/YYYY') AS DATECREATED,
FROM TABLE_A;
Then in another LOAD statement I am trying to do the comparison:
LOAD *
RESIDENT TABLE_A
WHERE DATECREATED > $(vTwoWeeksAgo);
I "think" the problem is that I have two different types of dates. My DateCreated field has the dates formatted first as YYYYMMDD, which I have converted to MM/DD/YYYY. I then want to be able to find records that have this date greater than my variable. However, I think my variable is being represented as the epoch date, a five digit date. Please, what am I doing wrong? Thanks so much
- Tags:
- qlikview_scripting
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Where possible, use number representation of dates, especially for comparisons. Leave text representation of dates only for UI objects.
LET vTwoWeeksAgo=num(Today()-14);
...
NUM(DATE#(PDDCR, 'YYYYMMDD')) AS DATECREATEDNUM,
...
WHERE DATECREATEDNUM > $(vTwoWeeksAgo);


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dear,
Try to Create the vTwoWeeksAgo format same as PDDCR field (YYYYMMDD)
like
LET vTwoWeeksAgo=Date(Today()-14,'YYYYMMDD');
Thanks,
Mukram.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
One 'tricky' thing in QlikView is the fact that a date field is dual (its a numerics formatted acoording to a format), but a variable is not dual.
So, when you do
LET vTwoWeeksAgo=Date(Today()-14,'YYYYMMDD');
vTwoWeeksAgo is a string, correct?
So it when you use that string you have to take care of this issue.
When I do comparisons I always format to a 'string-like comparision' 'YYYYMMDD'
I would rewrite your code to:
- LET vTwoWeeksAgo=Date(Today()-14,'YYYYMMDD');
- LOAD *
- RESIDENT TABLE_A
- WHERE date(DATECREATED,'YYYYMMDD') > '$(vTwoWeeksAgo)';

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you both for your help. I have tried your suggestions but unfortunately still cannot get the comparison to work. At present, this is what I have:
SET vTwoWeeksAgo='';
LET vTwoWeeksAgo=Date(Today()-14,'YYYYMMDD');
TEST:
LOAD
DATE#(PDDCR,'YYYYMMDD') AS DATECREATED
FROM TABLE_A;
TEST2:
LOAD *
RESIDENT TEST
WHERE DATECREATED > $(vTwoWeeksAgo);

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Where possible, use number representation of dates, especially for comparisons. Leave text representation of dates only for UI objects.
LET vTwoWeeksAgo=num(Today()-14);
...
NUM(DATE#(PDDCR, 'YYYYMMDD')) AS DATECREATEDNUM,
...
WHERE DATECREATEDNUM > $(vTwoWeeksAgo);
