Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Load date/time as date

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

1 Solution

Accepted Solutions
ashwanin
Not applicable

Re: Load date/time as date

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);

9 Replies
Not applicable

Re: Load date/time as date

Hi,

Use this while loading data

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

Thanks,

Prabhu

jontydkpi
Not applicable

Re: Load date/time as date

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Load date/time as date

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);

Not applicable

Re: Load date/time as date

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);

Not applicable

Re: Load date/time as date

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);

Not applicable

Re: Load date/time as date

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);

Not applicable

Re: Load date/time as date

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

Not applicable

Re: Load date/time as date

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

ashwanin
Not applicable

Re: Load date/time as date

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);