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]

Don't have any dates in this app that works at the moment unfortunately. Tried other tables, with the same result.

Is there a way that I can import the information to see what it is? I tried now to convert this value to text, and num, using the load... format you gave, but get the same result.

[johnw]

So you DON'T have any dates in that format? And you apparently DON'T have any dates in the MM/DD/YYYY format? OK, I think I see where I got lost, then, which is that you only saw those two formats when reading in a sample flat file from the tutorials. These are NOT the date formats in your actual Firebird Database source? And one problem is that you don't KNOW what the format in your Firebird Database source is? So you would like to import the information to see what it looks like?

Well, if it's showing squares in the import wizard, I'm not sure there's any way to import it into QlikView to have a look, though I could be wrong. What I would suggest is looking to see how the field is defined in the Firebird Database. Is it a text field? An SQL date? A numeric field? If a numeric field, is it a simple integer? Floating point? Binary? Basically, you can't read it in until you know what it is. Mind you, I think we can be fairly confident that it isn't a text field or an integer, or we probably wouldn't be seeing boxes.

[burgersurfer]

Hi John

When extracting data from the db I get the result, in a csv file, in DD/MM/YYYY format. Correct, I do not know what format is is stored in, getting the format shown here out on extracts lead me to believe that is the stored format.

Qlikview seems to "understand" it though - I can select and work with the dates in blocks.

[prieper]

Have you tried to identify the dataformat in the preview-function of the filewizard?

Peter

[burgersurfer]

Hi Peter

Yes I checked the format in the preview, and get the same squares as in the screenshots.

[prieper]

What about changing the ODBC-driver to a newer/older version?
On a quick check noticed on the Firebird-site that there might be some issues with certain data-types.
What about linking same tables into other databases (e.g. MS-Access)?

Peter

[johnw]

So you extract from the Firebird DB to a CSV file. In the CSV file, the date format is DD/MM/YYYY. You then load from the CSV file into a QlikView table. On that load, you would specify the input format in a date#() function, and possibly the output format in a date() function. For instance, if you wanted to convert from DD/MM/YYYY to MM/DD/YY, it would look like this:

date(date#(NB_DATE,'DD/MM/YYYY'),'MM/DD/YY') as NB_DATE,

That is then a QlikView date with a default format of MM/DD/YY. Then my understanding is that you store this as a QVD. The QVD will store it as a QlikView date with that default format. When you load it into your application from the QVD, it should be as simple as:

NB_DATE,

It's already in the right storage and display format, so you only have to tell QlikView to load it.

I gather that things are going horribly wrong somewhere in this process, but I'm honestly not clear where and how, and am kind of out of ideas as far as debugging it through a forum goes.

[#9]

try using the Same driver to import into excel and see what you get.

Also check the CodePage(s) being used if your moving the data across timezones or different Harware/OS

[burgersurfer]

Hi, thanks again for the input.

John, I connect directly to the firebird db to import data, trying to keep as close to the source as possible. Doing some searches, it looks like the fb normally uses one of 3 formats - DD.MM.YYYY, MM/DD/YYYY
YYYY-MM-DD. None of these worked

Then imported the data with the same odbc driver to Access - thanks ColinR - the format for the same NB_DATE I get here, is M/D/YYYY

Tried all the formats explained above, and still get the "blocks". I am totally confused.

[#11]

M/D/YYYY is not a standard format i would expect Access to use by default so it is being told either by OS regional setting or the driver itself to override default behaviour, but now it is in access you can at least run some functions on it in access using access queries, try Len(),isdate(),isnum() Str() ltrim() trim() datepart() etc. and a export from access to csv with a file compare on the result using windiff to the original export file

find a record with todays date and check that the num() function returns the same value for num(Date()) as num(yourfield). just because it displays a formatted date doesnt mean its the correct one.

There has to be a reason QV cant display it (the significance of the box is invalid character) we are all expecting a number made up of 0-9 but perhaps its got Tabs or other delimiters in there .

change your regional Date settings, Check any regional settings in the Driver config (odbc options) and ensure the shortdate and timestamp settings are set the same way across all of them.

Another option may be to turn on odbc trace options if its set up as an ODBC DSN