Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Specialist
Specialist

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

View solution in original post

9 Replies
Not applicable
Author

Hi,

Use this while loading data

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

Thanks,

Prabhu

jonathandienst
Partner - Champion III
Partner - Champion III

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
Author

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
Author

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
Author

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
Author

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
Author

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
Author

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
Specialist
Specialist

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