Question on date formatting in QV

[burgersurfer]

Hi

I do hope someone can help me with importing dates to Qlikview. I am using a Firebird db as source and importing budget data per store, per day into QV. The data seems to exist, but not displayed correctly.

As is showed in the screenshot, I can actually click and select a date and QV recognises it, but the I cannot see any dates

I tried unsuccessfully to format the date with Date( ), Date#( ), Day( ) etc, no luck.

Any help please?

[burgersurfer]

Looks like some progress!

When I import the data from Firebird to Access to QV, I get the format as attached. Can you maybe give me the correct script to read this?

Tried DD/MM/YYYY hh:mm:ss TT to no avail.

[johnw]

Well, it seems like you're now loading it successfully into QlikView. Looks like it's being read in as a timestamp rather than a date. That's probably OK, but you may want to strip off the time portion of it. What current expression are you using on the load? To be certain you're stripping off the time, and format it how you want (let's say MM/DD/YY for the sake of argument), perhaps something like this?

date(floor(your current expression here),'MM/DD/YY') as NB_DATE,

[burgersurfer]

Hi John

I have yet to solve my original problem, which is loading dates from the Firebird database to QV. What I achieved in the previous post is to find a way to manipulate the data for QV to understand, by adding a staging database into the mix, this was done only to see how other tools (in this case Access) reads the data from Firebird. This cannot be a permanent solution.

To answer your question, this is my script to load data from Access:

 Normal 0 false false false EN-US X-NONE X-NONE
ODBC CONNECT TO [MS AccessDatabase;DBQ=\\netkeeper\administrator\My
Documents\DateDB2.mdb];


SQL SELECT `NB_STORE_ID`,
 `NB_DATE`,
 `NB_VALUE`,
 `NB_TARGET`
FROM `NB_BUDGETS`;

I still need to load date and time information from Firebird, and in my limited opinion, this must be done in 1 or 2 ways -

1 - either find the right way to handle firebird dates, or

2 - define a date table somewhere, import (or if done in QV, load it) and then somehow associate this "island of dates" with the dates information obtained from Firebird.

It still feels that the format I get when importing to Access is masked by Access's own criteria, and this is then transferred to QV easily. But I need to understand how Firebird sees the data, and to do that, maybe telling QV this field is NOT a date, but e.g. a text value might help. Is there a convert to text function I am missing?

[#5]

does firebird have any date functions,

Ie export day month and year as seperate fields, these can then be concatenated in QV to a date

[burgersurfer]

Not sure, but I do not have access to the database other than odbc, so everything I can do starts from the point of extracting the data.

[burgersurfer]

Hi all

So I fiddled more with this today - but still no real solution. I now found tools to extract, and to investigate the Firebird Database, with both I see the date in Firebird as mm-dd-yyyy - something like 5/13/2009. But in no way can I get the data from Firebird into QV such that it can be read by a person.

The workaround for now, is to send all date-related tables to Access, and from there to QV. This gives me a timestamp in QV, next step now is to create a master calendar from the data.

Not ideal as it creates too many steps which = possible errors and unnecessary user intervention, but that is where we are now.

Thanks all for the numerous suggestions, if a result does come up I will be sure to post here.