topic Question on date formatting in QV in QlikView

Question on date formatting in QV

burgersurfer — Fri, 15 Jan 2010 19:09:33 GMT

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?

Question on date formatting in QV

Fri, 15 Jan 2010 21:10:32 GMT

Are you sure you used date#() correctly?

It needs the format the date from the Firebird db is in to create a QV date.

Have you tried exporting the data as a flat-file first?

Question on date formatting in QV

hector — Fri, 15 Jan 2010 21:18:46 GMT

hi, are you sure if that is not a problem with the odbc driver? i'm saying this because some reasons:

When you apply the functions date#() the value in the selector is null, but you are showing some values
The value (i mean the squares) is left aligned, when a date is valid this is right aligned (internally a number), unless you forced the alignment

my suggestions:

Have you tried read the data from another software? maybe is a problem with the driver or a configuration
can you show us the real value of the field? i mean, don't apply any functions. so we can check the format and help you with the functions or date#() format

my experience with AS/400

The flag "Convert binary data to text" must be checked

rgds and good luck

Question on date formatting in QV

burgersurfer — Fri, 15 Jan 2010 21:32:22 GMT

Hector/Mark, thanks for the replies.

I used a sample flat file from the tutorials to test date imports - this works fine and I get Julian and mm/dd/yyyy dates in, and can format it.

Also exported the table to csv files from the original app - the date format in this file is dd/mm/yyyy. I could not see any manupulation actions to get the data to CSV, so I assume this is the format, and then the code looks like this:



SQL SELECT "NB_STORE_ID",
 //"NB_DATE",
 Date#("NB_DATE" , 'dd/mm/yyyy' ) as "budate",
 "NB_VALUE",
 "NB_TARGET"
FROM "NK_BUDGETS";

The commented part gives the results shown in my screenshot, the next line is my attempt at manipulating it - this gives an error though. Not changing the name does not help either.

Question on date formatting in QV

johnw — Fri, 15 Jan 2010 21:38:53 GMT

Date#() is a QlikView function, so needs to be done outside of the SQL (which is just being passed to your DBMS, which has no idea what to do with it). In other words:

LOAD *
,date#(NB_DATE,'DD/MM/YYYY') as budate
;
SQL SELECT
"NB_STORE_ID"
,"NB_DATE"
,"NB_VALUE"
,"NB_TARGET"
FROM "NK_BUDGETS"
;
DROP FIELD NB_DATE
;

I don't guarantee that's the actual script you'll need, but it at least has the date#() in the right place.

Question on date formatting in QV

burgersurfer — Fri, 15 Jan 2010 21:49:16 GMT

Thanks John, at least I do not get errors anymore.Your code gives me the original result, so I will now try different date formats. Is there a way to specify Julian dates as the source for QV? (numeric dates e.g. 34566)

Question on date formatting in QV

burgersurfer — Fri, 15 Jan 2010 21:55:11 GMT

Oh, the dates show the same "squares" in the import wizard as well - when you preview the table in "Create Select statement" dialog. '

Driver issue then?

Question on date formatting in QV

johnw — Fri, 15 Jan 2010 21:55:29 GMT

QlikView doesn't have formatting codes for Julian dates, but you can still work it out with a more complicated expression. However, different people mean different things by Julian dates. What date is 34566? How is it read?

Question on date formatting in QV

burgersurfer — Fri, 15 Jan 2010 22:08:29 GMT

I understand it to be a numeric representation of the date, x days from 1 Jan 1900 (I think). The same format as in the help file, when you search for date, the date

1997-08-06 is represented by 35648.

Does that help?

Question on date formatting in QV

johnw — Fri, 15 Jan 2010 22:18:15 GMT

OK, if you have dates somewhere that are already in the QlikView numeric format, you don't need date#() at all. You just need to tell QlikView to interpret that number as a date, which should be date(YourInputDate) as YourDate.

Question on date formatting in QV

burgersurfer — Fri, 15 Jan 2010 22:25:07 GMT

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.

Question on date formatting in QV

johnw — Fri, 15 Jan 2010 23:08:05 GMT

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.

Question on date formatting in QV

burgersurfer — Sun, 17 Jan 2010 18:17:03 GMT

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.

Question on date formatting in QV

prieper — Sun, 17 Jan 2010 20:11:53 GMT

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

Peter

Question on date formatting in QV

burgersurfer — Mon, 18 Jan 2010 08:02:13 GMT

Hi Peter

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

Question on date formatting in QV

prieper — Mon, 18 Jan 2010 09:32:17 GMT

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

Question on date formatting in QV

johnw — Mon, 18 Jan 2010 19:47:40 GMT

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.

Question on date formatting in QV

Mon, 18 Jan 2010 20:04:44 GMT

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

Question on date formatting in QV

burgersurfer — Mon, 18 Jan 2010 22:13:14 GMT

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.

Question on date formatting in QV

Mon, 18 Jan 2010 22:41:27 GMT

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