topic Re: Exists is Not Working With Timestamps in QlikView

Exists is Not Working With Timestamps

Wed, 22 Jun 2016 17:41:24 GMT

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.

Re: Exists is Not Working With Timestamps

swuehl — Wed, 22 Jun 2016 19:32:21 GMT

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?

Re: Exists is Not Working With Timestamps

Wed, 22 Jun 2016 19:36:26 GMT

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

Re: Exists is Not Working With Timestamps

swuehl — Wed, 22 Jun 2016 20:23:22 GMT

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

Colin-Albert — Wed, 22 Jun 2016 20:28:36 GMT

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

marcus_sommer — Thu, 23 Jun 2016 06:06:36 GMT

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

sunny_talwar — Fri, 24 Jun 2016 05:40:06 GMT

What is the script that you are using?