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?

[#2]

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?

[hector]

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

[burgersurfer]

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.

[johnw]

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.

[burgersurfer]

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)

[burgersurfer]

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?

[johnw]

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?

[burgersurfer]

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?

[johnw]

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.