topic Convert Datefield in QlikView

Convert Datefield

fred_s — Mon, 19 Oct 2009 20:46:51 GMT

I'm a newbie. Don't be too hard... [:)]

We have this weird kind of database, no sql, mysql or whatever.
This database supports ODBC, but only the basic stuff.
For most fields no problem, except Date fields.

Every Date comes in as a String (but documentprops says it's a date...).
Set Dateformat in script is correct ('YYYY-M-D').

"month (DateX) as Datex_month" doesn't do his job.

Question 1:
Is there a possibility, after (Re)loading the data, to convert this field (maybe to a new and real Date field?)?

Any ideas or Workarounds?

Question 2:
Most of the Qlikview examples split dates in to year, month and day.
Since I can't split these fields, is there a clever way to select Dates?
We can use * search for text and <=> for Numeric fields, but I have no idea how to select Dates.
So far no luck with > or between functions

Thanks in advance

Fred

Convert Datefield

Mon, 19 Oct 2009 20:54:10 GMT

What yyyymmdd format does your "date" field pull in? Try using the date() and date#() functions to convert your text field into a true date field in qlikview.

Convert Datefield

Mon, 19 Oct 2009 20:55:52 GMT

Hi Fred,

if it is a text field than write:

Date(Date#(DateX, 'YYYY-M-D'), 'DD-MM-YYYY') as MyDateFormat

Date# is for interpretation and Date to format your data.

If it does the job you can split it to YEAR and so on as follows:

Year(MyDateFormat) as Year,

Month(MyDateFormat) as Month

If it doesn´t work please provide some data as example.

Good luck!

Rainer

Convert Datefield

fred_s — Mon, 19 Oct 2009 21:47:04 GMT

Actually I've tried this one, but it seems like
"Date(Date#(DateX, 'YYYY-M-D'), 'DD-MM-YYYY') as MyDateFormat" is not supported:

SQL Error:Syntax error or access violation
SQL Scriptline:
SQL State:37000

The fact that these Date/Date# functions aren't supported is probably my problem.

The database returns Dates in 'Dutch' format (DD-MM-YYYY) so I've changed the DateFormat in the script back to Dutch instead of YYYY-M-D.

As mentioned earlier, my dates are probably string (left alignment in Qlikview where Excel example dates have Right alignment).
According to documentproperties it should be a Date.

I have a little example attached, where Factuurdat is the problem.
Datum, mnd and Omschr are fields from an Excel file, just for me to show that it is possible to split Dates.

Hope you guys can offer me a workaround...

Thanks!

Convert Datefield

GabrielAraya — Mon, 19 Oct 2009 21:50:58 GMT

Hi, the Date# is a QV function, I recomend:

Load

Date(Date#( .... )

;

Sql Select ........ ;

Gabriel

Convert Datefield

fred_s — Mon, 19 Oct 2009 22:07:13 GMT

Hi Gabriel,

Thanks for your reply.

The problem is, the SQL string in QV is some kind of Pass Through-query like the ones in MS Access.
QV doesn't do anything with the string, just passes it through.

The problem is 'Date' or 'Date#' are no QV functions, but part of the (Pass Through) SQL string.
It seems my ODBC connection does not support Date functions.

Convert Datefield

GabrielAraya — Mon, 19 Oct 2009 22:14:40 GMT

Hi fred,

I don't thing that the problem is with the connection string .. (ODBC ..... or OLEDB ... ), my suggestion was to work with a LOAD before the SQL statement, the reason is the Date# is a QlikView function ...so:
Load
Field1,
Field2,
Date(Date#(field3,'DD-MM-YYYY')) as field3
;
Sql Select Field1, Field2, Field3 from AccesTable;

Gabriel

Convert Datefield

fred_s — Mon, 19 Oct 2009 22:44:51 GMT

Hi Gabriel,

It took me a couple of seconds to get your point, but this looks great!!

Can you tell me how to split this Date into Year, Month and Day?
Something like
Year(Factdatum) as Factjaar

It doesn't work in the load and it doesn't in the SQL string.

Thanks!
It's already a great solution and still can get better...

Current code:

ODBC CONNECT TO SERVERX (XUserId is 5151515151515, XPassword is 7474747474747);
Load
 Boekjaar,
 [Factuur-nr],
 Factuurbedr,
 Date(Date#(Factuurdat,'DD-MM-YYYY')) as Factdatum
;
SQL SELECT Boekjaar,
 Factuur-nr ,
 Factuurbedr,
 Factuurdat
FROM _DF_Debiteur_factuur ;

Convert Datefield

GabrielAraya — Mon, 19 Oct 2009 22:51:14 GMT

Of Course, it is very simple, you can add a new line after Date(Date#(.......) as Factdatum,

Year(Date#(.....)) as YearFactdatum,
Month(Date#(...)) as MonthFactdatum .. and so on

Gabriel

Convert Datefield

rwunderlich — Tue, 20 Oct 2009 04:25:15 GMT

You may also use an additional preceeding load to avoid repeating the date#() function. Like this:

LOAD *,
year(Factdatum) as YearFactdatum,
month(Factdatum) as MonthFactdatum
;
Load
Boekjaar,
[Factuur-nr],
Factuurbedr,
Date(Date#(Factuurdat,'DD-MM-YYYY')) as Factdatum
;
SQL SELECT Boekjaar,
Factuur-nr ,
Factuurbedr,
Factuurdat
FROM _DF_Debiteur_factuur ;

-Rob

Convert Datefield

johnw — Tue, 20 Oct 2009 07:42:16 GMT

Or create a separate calendar table after loading the main data. I also usually prefer my year and month be actual dates:

[Calendar]:
LOAD
Factdatum
,date(yearstart(Factdatum),'YYYY') as YearFactdatum
,date(monthstart(Factdatum),'MMM YYYY') as MonthFactdatum
;
LOAD fieldvalue('Factdatum',iterno()) as Factdatum
AUTOGENERATE 1
WHILE len(fieldvalue('Factdatum',iterno()))
;

Convert Datefield

fred_s — Tue, 20 Oct 2009 19:49:24 GMT

Hi,

Thanks, especially Gabriel!

You All have been a great help.
It has been a pain for several weeks. Next time I won't wait that long.

Rob and John, thanks for the addition.

All problems solved (so far 😉

Fred