Exists is Not Working With Timestamps

[#1]

1.) I have 20 million rows of data, there is a timestamp for each of them (MM/DD/YYYY) and the data goes back 6 years.

2.) I created a table of dates going back 12 months (MM/DD/YYYY).

3.) Both timestamps have the same alias (TimestampName).

From what I understand about the Exists statements, this should allow me to use a Where Exists(TimestampName) statement and only load rows with a timestamp in the past 12 months.

Am I wrong about this?

*Note* When I try this, I get about 40 completely random rows with no correlation with the TimestampName.

*Note* When I do a simple Where TimestampName >= date([12months ago]) it works and I get around 2 million rows.

[swuehl]

Exists() basically checks on identical values in the symbol table so it will only filter your input table if you have loaded the exact same values before.

I assume your table with the 12 months are basically showing dates, i.e. timestamps only for midnight (AKA integer values).

Your timestamps may show any time of the day? Then you are effectively only filtering the timestamps that are recorded exactely around midnight.

edit:

Just for clarification, your timestamp field may be formatted as date, but still show numeric values with a time part.

Please have a look at

Why don’t my dates work?

[#3]

The neither timestamps hold hour/minute/second information, they are all in the MM/DD/YYYY format.

[swuehl]

Ok, could you post a small sample QVW that demonstrates your issue?

This QVW could just show your two date fields.

Uploading a Sample

[Colin-Albert]

Are your timestamps held as  a) text values,

b) dual dates with an integer numeric value & text

Or c) dual dates including time i.e a floating numeric value & text

Depending on exactly how your timestamps are held in the data model will affect how the comparison in the Where Exists will work.

[marcus_sommer]

The format won't be important in this case - the numeric value is. To ensure that's really a date and not a timestamp you need to round these values like: date(floor(TimestampName)).

- Marcus

[sunny_talwar]

What is the script that you are using?