Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

thnkgreen
Contributor

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  

1 Solution

Accepted Solutions
Not applicable

Re: String Date YYYYMMDD > Epoch date comparison

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

4 Replies
mdmukramali
Valued Contributor II

Re: String Date YYYYMMDD > Epoch date comparison

Dear,

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

like

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

Thanks,

Mukram.

Employee
Employee

Re: String Date YYYYMMDD > Epoch date comparison

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

thnkgreen
Contributor

Re: String Date YYYYMMDD > Epoch date comparison

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

Not applicable

Re: String Date YYYYMMDD > Epoch date comparison

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

Community Browser