String Date YYYYMMDD > Epoch date comparison

[Anonymous]

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  

[mdmukramali]

Dear,

Try to Create the  vTwoWeeksAgo format same as PDDCR field (YYYYMMDD)

like

LET vTwoWeeksAgo=Date(Today()-14,'YYYYMMDD');

Thanks,

Mukram.

[Clever_Anjos]

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:

1. LET vTwoWeeksAgo=Date(Today()-14,'YYYYMMDD');
2. LOAD * 
3. RESIDENT TABLE_A 
4. WHERE date(DATECREATED,'YYYYMMDD') > '$(vTwoWeeksAgo)'; 

[Anonymous]

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

[#5 · Accepted solution]

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