topic Load date/time as date in QlikView

Load date/time as date

Tue, 01 Apr 2014 08:14:03 GMT

I have a csv file I am loading with a date/time field called SessionStarted (e.g. 28/03/2014 08:56).

I need to either load it as a date, or edit my expressions to read the data as a date. Or is it even possible to split the data into two fields when doing the load?

One of my expressions is as follows:

Count({$<UserName-={"*test*"},Firm-={"Acme"}>}UserName)

Thanks

Alex

Re: Load date/time as date

Tue, 01 Apr 2014 08:19:51 GMT

Hi,

Use this while loading data

Date(Timestamp#(SessionStarted ,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY')

Thanks,

Prabhu

Re: Load date/time as date

jonathandienst — Tue, 01 Apr 2014 08:26:52 GMT

You can split the field at load using (in fact I would recommend it):

Date(Floor(TimeStamp#(SessionStarted, 'DD/MM/YYYY hh:mm'))) As Date,

Time(Frac(TimeStamp#(SessionStarted, 'DD/MM/YYYY hh:mm'))) As Time,

Note that Date() without Floor does not remove the time portion, it merely reformats the field; so you will land up with a whole lot of seemingly identical but actually unique values for each day. This is a common mistake. The Frac does the equivalent for the time.

HTH

Jonathan

Re: Load date/time as date

Tue, 01 Apr 2014 08:29:11 GMT

So, how would that fit into my load script?

LOAD UserName,

SessionId,

Environment,

DisconnectionReason,

SessionStarted,

WorkingDayReconnect

FROM

[\\XXXXXX\XXXXXX\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

Re: Load date/time as date

Tue, 01 Apr 2014 08:31:00 GMT

That's great Jonathan. I'm a bit of a beginner at this, so how would that fit into my load script?

LOAD UserName,

SessionId,

Environment,

DisconnectionReason,

SessionStarted,

WorkingDayReconnect

FROM

[\\XXXXXX\XXXXXX\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

Re: Load date/time as date

Tue, 01 Apr 2014 08:53:32 GMT

I tried simply replacing the SessionStarted line with your script, but it just seems to load blank values for the date

LOAD UserName,

SessionId,

Environment,

DisconnectionReason,

Date(Floor(TimeStamp#(SessionStarted, 'DD/MM/YYYY hh:mm'))) As CalendarDate,

Time(Frac(TimeStamp#(SessionStarted, 'DD/MM/YYYY hh:mm'))) As Time,

WorkingDayReconnect

FROM

[\\XXXXXX\XXXXXX\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

Re: Load date/time as date

Tue, 01 Apr 2014 08:58:32 GMT

Try this..

LOAD UserName,

SessionId,

Environment,

DisconnectionReason,

Date(Timestamp#(SessionStarted ,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY') as SessionStarted,

SessionStarted as SessionStartedTime,

WorkingDayReconnect

FROM

[\\XXXXXX\XXXXXX\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

Re: Load date/time as date

Tue, 01 Apr 2014 09:08:33 GMT

I get the same thing as which Jonathan's suggestion below. The SessionStarted data which should show the date is blank for all rows

Re: Load date/time as date

Tue, 01 Apr 2014 09:40:28 GMT

Could you pls give some sample data that SessionStarted field contains?

It is just to check the format of the data time in it.

Thanks,

Prabhu

Re: Load date/time as date

ashwanin — Tue, 01 Apr 2014 09:44:44 GMT

LOAD UserName,

SessionId,

Environment,

DisconnectionReason,

Date(SessionStarted,'DD-MMM-YYYY') As Date,

WorkingDayReconnect

FROM

[\\XXXXXX\XXXXXX\*.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);