topic Re: Transferring a text value into date when loading in the data in QlikView

Transferring a text value into date when loading in the data

Tue, 02 Aug 2016 11:01:26 GMT

Dear all,

I have got data that is structured like this:

Date
4-1-2016 18:13:23

As I would like to have the value as DDMMYYYY I try to load the data like this:

    ODBC CONNECT32 TO [DWH_P3.world;DBQ=DWH_P3] (XUserId is BQYVFIBHWBMOTcAHbCTCEBdLTKREFLFJDKRGVOVD,   XPassword is dUbMAYVMQbcSWYIGTBSB);
    LOAD WEEK_IN,
     Date#(OFF_SWITCH_DAT, 'DD-MM-YYYY');

SQL SELECT *

FROM "LAB_SERVICES"."FBI_LKR_OFF_UITVAL";

This however gives the following:

Date
42295

So the date format seems wrong. Any thoughts on what goes wrong here?

Re: Transferring a text value into date when loading in the data

Anonymous — Tue, 02 Aug 2016 11:06:51 GMT

you have a timestamp AS FORMAT

use:

date(floor(date#('4-1-2016 18:13:23','DD-MM-YYYY hh:mm:ss')))

Re: Transferring a text value into date when loading in the data

marcus_sommer — Tue, 02 Aug 2016 11:06:56 GMT

Try this:

date(floor(timestamp#('4-1-2016 18:13:23', 'DD-MM-YYYY hh:mm:ss')), 'DDMMYYYY')

- Marcus

Re: Transferring a text value into date when loading in the data

sunny_talwar — Tue, 02 Aug 2016 11:10:19 GMT

Try this:

ODBC CONNECT32 TO [DWH_P3.world;DBQ=DWH_P3] (XUserId is BQYVFIBHWBMOTcAHbCTCEBdLTKREFLFJDKRGVOVD, XPassword is dUbMAYVMQbcSWYIGTBSB);

LOAD WEEK_IN,
Date(Floor(Date#(OFF_SWITCH_DAT, 'D-M-YYYY h:mm:ss'))) as Date;

SQL SELECT *

FROM "LAB_SERVICES"."FBI_LKR_OFF_UITVAL";

Re: Transferring a text value into date when loading in the data

Tue, 02 Aug 2016 12:17:02 GMT

Guys, thanks for your input but neither of the suggestions work. I still get blank values... Any thoughts on what else I can do??

Re: Transferring a text value into date when loading in the data

sunny_talwar — Tue, 02 Aug 2016 12:23:21 GMT

How about this:

Date(SubField(OFF_SWITCH_DAT, ' ', 1)) as Date;

Re: Transferring a text value into date when loading in the data

Anonymous — Tue, 02 Aug 2016 12:25:41 GMT

try this

Date(Date#(4-1-2016 18:13:23,'DD-MM-YYYY hh:mm:ss'),'DDMMYYYY')

Re: Transferring a text value into date when loading in the data

Tue, 02 Aug 2016 13:17:01 GMT

pleas check it

Tab1:

LOAD Date,

date(Date#(Date(Date),'DD-MM-YYYY'),'DDMMYYYY') as date

//date(floor(timestamp#('Date', 'DD-MM-YYYY hh:mm:ss')), 'DDMMYYYY') as date5

FROM

(ooxml, embedded labels, table is Sheet1);

Re: Transferring a text value into date when loading in the data

Anonymous — Tue, 02 Aug 2016 13:24:50 GMT

Hope this works:

Date(Date#('4-1-2016 18:13:23','DD-MM-YYYY hh:mm:ss'),'DDMMYYYY')

Re: Transferring a text value into date when loading in the data

Tue, 02 Aug 2016 13:29:19 GMT

Before trying this please maintain below format

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff] TT';

date(Date#(Date(Date),'DD-MM-YYYY'),'DDMMYYYY') as date

I have got result

Date date

01-04-2016 16:13:23 Pm 01042016