Weird Date/String interaction

[#1]

In my Select statement, pulling from the same database, which has the same date format for both fields, I have:

Coalesce(OM.StartDate,'1/1/1989') AS "Opp_StartDate",
Coalesce(OM.ClosedDate,'1/1/1989') AS "Opp_ClosedDate",

In my “Activity” table, I have for two calculated dimensions, Start Date and Close Date, the following expressions (because I sick of seeing the 12:00:00 AM appended to each):

=left(text(Opp_StartDate),10)

=left(text(Opp_ClosedDate),10)

The Start Date column looks fine (e.g. “5/24/2012”), except some entries are right aligned (i.e. numeric), and some are left aligned (i.e. text).

However, the Close Date column is all the date integers (e.g. 41418, 41419, etc.)

Can anyone help me figure out what’s going on?

[Miguel_Angel_Baeyens]

Hi,

Most likely it's because of the Left() and Text() functions. If you want to get rid of the time part, then use the function Floor()

Date(Floor(Opp_StartDate)) AS Opp_StartDate

This will keep the integer part, which is for QlikView the date part. Those numbers are QlikView dates, using the function Date() on them you will see something like "04/06/2012".

Hope that helps.

Miguel

[swuehl]

Kevin,

try reading in your dates as QV date type from the beginning (so no need to use string functions later on to crop the date part).

If you are using date format D/M/YYYY or M/D/YYYY all over your application, set the standard date format in your script to this format:

SET DateFormat='M/D/YYYY';

Also, try matching your timestamp (your read in data seems to be time stamps):

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

If you don't want to use these formats as standard format, use date#() or timestamp#() function to interprete your input:

LOAD

timestamp#(Opp_StartDate,'M/D/YYYY h:mm:ss TT') as Opp_StartDateTS,

Date(daystart(timestamp#(Opp_StartDate,'M/D/YYYY h:mm:ss TT'))) as Opp_StartDate;

Select

Coalesce(OM.StartDate,'1/1/1989') AS "Opp_StartDate",

....

FROM...;

Note how I used daystart(TIMESTAMPFIELD) function to get only the integer / date part.

Hope this helps,

Stefan

[#4]

Miguel, thanks, that worked, although again, I notice when I ask what to do in a TABLE within QV, most of the responses are what to do with the SCRIPT.  I've learned a bit about using functions in the tables, so it worked.

But, my original question is still unanswered, and I'd like to know why (as opposed to how to work around it). If you have two dates, in exactly the same format (because before I tried doing this, both date fields showed exactly the same numbers, down to the microsecond..), why would using the same functions give you: an "MM/DD/YYYY" resulted formated as text in one case, numeric in another, and finally as a date number (48818, etc.) in the third? Non-deterministic computers scare me.

[Miguel_Angel_Baeyens]

Hi Kevin,

Yes, I agree partially with you. Regarding the script vs. charts, and if there is a such thing as a "golden rule" in QlikView is speed and performance: the simpler the expressions are, the faster they usually perform. And in order to simplify expressions, do as many things in the script as you need. That's why the LOAD and all the functions are available in QlikView.

In short, use QlikView as much as you can, and SQL as little as you can, or put in different words, leverage QlikView and release the transactional/odbc/source. Of course, when possible and feasible.

In regards to dates, well, it depends very much on the data source. Although in QlikView there aren't types as in a RDBM (integer, double, float, date, char, varchar, date, time...) and all values should be represented either numeric or literal (string), QlikView does note how a field comes from a source. What seems the same between Excel and SQL Server in regards to dates may not be the same underlying value. And dates are very very sensitive in QlikView as in any other database.

My guess not having the data sources? First, it depends on how the field has been defined in the database (date and timestamp are not the same, the latter has hours, minutes and seconds). Second, it depends on the actual values, because although in the interface seems the same when you input "05/06/2012 00:00:00" than "05/06/2012", they are not actually the same, and so QlikView may represent the former as a text and the latter as a number.

So, are all sources defined the same? If the answer is yes, then I don't even dare to guess... My experience says that the more I refine data when pulled from sources (using LOAD and functions) the lesser problems you will find later on.

Hope some of the above made sense.

Miguel

[#6]

Hi everyone, I've found an even simpler method to solve this problem - I just go to the "number" tab, select the field, select "Override Document Settings", and then pick "Date", and the "MM/DD/YYYY" format. Works fine!