Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Exists is Not Working With Timestamps

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.

6 Replies
swuehl
MVP
MVP

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?

Not applicable
Author

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

swuehl
MVP
MVP

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
MVP & Luminary
MVP & Luminary

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?