Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Re: Exists is Not Working With Timestamps

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

Re: Exists is Not Working With Timestamps

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

MVP
MVP

Re: Exists is Not Working With Timestamps

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

This QVW could just show your two date fields.

Uploading a Sample

Re: Exists is Not Working With Timestamps

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.

Re: Exists is Not Working With Timestamps

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

Re: Exists is Not Working With Timestamps

What is the script that you are using?

Community Browser