topic Re: String Date YYYYMMDD > Epoch date comparison in QlikView

String Date YYYYMMDD > Epoch date comparison

Anonymous — Thu, 03 Oct 2013 13:22:05 GMT

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

Re: String Date YYYYMMDD > Epoch date comparison

mdmukramali — Thu, 03 Oct 2013 13:34:44 GMT

Dear,

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

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

Thanks,

Mukram.

Re: String Date YYYYMMDD > Epoch date comparison

Clever_Anjos — Thu, 03 Oct 2013 14:02:18 GMT

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

Re: String Date YYYYMMDD > Epoch date comparison

Anonymous — Thu, 03 Oct 2013 14:23:55 GMT

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

Re: String Date YYYYMMDD > Epoch date comparison

Thu, 03 Oct 2013 14:33:38 GMT

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