Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Splitting time within a cell into multiple cells- Excel upload

Hi,


Can someone please help me? I would like to upload my script to break down below into two or more separate columns Date & Time. Right now, my information is gathered into one column.

Column 1

[13/Mar/2014:20:00:08+0000]

Preferably, I would like the script to be able to break down as per below.

Column 1 - YYYY

Column 2 - MM

Column 3 - DD

Column 4 - HH:MM:SS

I would also take below if above is too difficult

Column 1 - MM/DD/YYY

Column 2 - HH:MM:SS.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

This should do it:

LOAD *,

  Year([Request Time]) as Year,

  Month([Request Time]) as Month,

  Day([Request Time]) as Day,

  Time([Request Time]) as Time;

LOAD Timestamp(Timestamp#(Mid([Request Time],2,20),'DD/MMM/YYYY:hh:mm:ss')) as [Request Time],

     [HTTP Status],

     [Data Bytes],

     [Time to Serve Request (Microseconds)]

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

View solution in original post

3 Replies
swuehl
MVP
MVP

This should do it:

LOAD *,

  Year([Request Time]) as Year,

  Month([Request Time]) as Month,

  Day([Request Time]) as Day,

  Time([Request Time]) as Time;

LOAD Timestamp(Timestamp#(Mid([Request Time],2,20),'DD/MMM/YYYY:hh:mm:ss')) as [Request Time],

     [HTTP Status],

     [Data Bytes],

     [Time to Serve Request (Microseconds)]

FROM

[Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Not applicable
Author

I loaded per below and am able to execute successfully, however, when I select a Table Box selecting Year, Month, or Day, there are no values.

I would like to be able to use the Year, Month, Day fields rather than the Request Time field.

LOAD

Year([Request Time]) as Year,

Month([Request Time]) as Month,

Day([Request Time]) as Day,

Time([Request Time]) as Time,

Timestamp(Timestamp#(Mid([Request Time],2,20),'DD/MMM/YYYY:hh:mm:ss')) as [Request Time],

[HTTP Status],

[Data Bytes],

[Time to Serve Request (Microseconds)],

FROM

[..\..\20140313_Resource_2.xlsx]

(ooxml, embedded labels, table is resource);

Not applicable
Author

I figured it out.