topic Re: Splitting time within a cell into multiple cells- Excel upload in QlikView

Splitting time within a cell into multiple cells- Excel upload

Mon, 17 Mar 2014 22:49:08 GMT

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.

Re: Splitting time within a cell into multiple cells- Excel upload

swuehl — Mon, 17 Mar 2014 23:24:03 GMT

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

Re: Splitting time within a cell into multiple cells- Excel upload

Tue, 18 Mar 2014 00:06:51 GMT

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

Re: Splitting time within a cell into multiple cells- Excel upload

Tue, 18 Mar 2014 00:48:44 GMT

I figured it out.