Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

rt_new_user
New Contributor II

Formatting DATE Field (YYYY-MM-DD :hh:mm:SS) into two separate fields Date (yyyy-mm-dd) and time (hh:mm:ss)??

Dear community users

Can anyone suggest how do i format my Date field which is originally in this format (YYYY-MM-DD:hh:mmSmiley FrustratedS) into two separate list boxes named Date and time?

my script is:

LOAD @1 as Country,

     

     @5 as DATE,

     @6 as Duration,

     

     @11 as Technology

   

FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);

Screen Shot 12-18-14 at 01.15 PM 001.JPG

1 Solution

Accepted Solutions
jyothish8807
Honored Contributor II

Re: Formatting DATE Field (YYYY-MM-DD :hh:mm:SS) into two separate fields Date (yyyy-mm-dd) and time (hh:mm:ss)??

Hi Ravi, try like this:

In script:

LOAD @1 as Country,

     Date(timestamp#(@5,'YYYY-MM-DD:hh:mmSmiley FrustratedS'),'YYYY-MM') as Date,

     time(timestamp#(@5,'YYYY-MM-DD:hh:mmSmiley FrustratedS')) as Time,

     @6 as Duration,

     @11 as Technology

FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);

Now in front end create a bar chart:

In dimension:

Take month.

Inexpression:

1. count({<Technology={'2G'}>}Technology)

2.count({<Technology={'3G'}>}Technology)

Hope it helps:

Regards

KC

Best Regards,
KC
11 Replies

Re: Formatting DATE Field (YYYY-MM-DD :hh:mm:SS) into two separate fields Date (yyyy-mm-dd) and time (hh:mm:ss)??

date(floor(@5)) as DATE

time(frac(@5)) as TIME


-Rob

rt_new_user
New Contributor II

Re: Formatting DATE Field (YYYY-MM-DD :hh:mm:SS) into two separate fields Date (yyyy-mm-dd) and time (hh:mm:ss)??

Thanks for your reply Rob

Updating my script to this give me an error:

LOAD @1 as Country,

// @5 as DATE,

Date(floor@5) as DATE,

time(frac@5) as TIME,

@6 as Duration,

@11 as Technology

FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);

senpradip007
Valued Contributor III

Re: Formatting DATE Field (YYYY-MM-DD :hh:mm:SS) into two separate fields Date (yyyy-mm-dd) and time (hh:mm:ss)??

Try like:

LOAD @1 as Country,

Date(floor(@5)) as DATE,

time(frac(@5)) as TIME,

     @6 as Duration,

     @11 as Technology

FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);

rt_new_user
New Contributor II

Re: Formatting DATE Field (YYYY-MM-DD :hh:mm:SS) into two separate fields Date (yyyy-mm-dd) and time (hh:mm:ss)??

Thanks

Loading the below script didn’t give any error but no data loaded in the list box for DATE and TIME

LOAD @1 as Country,

// @5 as DATE,

Date(floor(@5)) as DATE,

time(frac(@5)) as TIME,

@6 as Duration,

@11 as Technology

FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);

sriharsha96
Contributor II

Re: Formatting DATE Field (YYYY-MM-DD :hh:mm:SS) into two separate fields Date (yyyy-mm-dd) and time (hh:mm:ss)??

Hi

if you want to do in list box then

Try adding list boxes and edit expressions as

fro date left (Date,8)

for time right(Dtae,7)

Regards

Harsha

jyothish8807
Honored Contributor II

Re: Formatting DATE Field (YYYY-MM-DD :hh:mm:SS) into two separate fields Date (yyyy-mm-dd) and time (hh:mm:ss)??

Hi Ravi,

Try

Date(timestamp#(@5,'YYYY-MM-DD:hh:mmSmiley FrustratedS')) as Date,

time(timestamp#(@5,'YYYY-MM-DD:hh:mmSmiley FrustratedS')) as Time

or

Try like this:

left(@5,10) as date,

right(@5,8) as Time

Hope it works

Regards

KC

Best Regards,
KC
rt_new_user
New Contributor II

Re: Formatting DATE Field (YYYY-MM-DD :hh:mm:SS) into two separate fields Date (yyyy-mm-dd) and time (hh:mm:ss)??

Hi Jyotish

That worked exactly the way i wanted. However can i just change my requirement slightly?

What if i want the date column just to display in YYYY-MM format?

Current Script is:

LOAD @1 as Country,

Date(timestamp#(@5,'YYYY-MM-DD:hh:mmSmiley FrustratedS')) as Date,

time(timestamp#(@5,'YYYY-MM-DD:hh:mmSmiley FrustratedS')) as Time,

@6 as Duration,

@11 as Technology

FROM

(txt, codepage is 1252, no labels, delimiter is ',', msq);

rt_new_user
New Contributor II

Re: Formatting DATE Field (YYYY-MM-DD :hh:mm:SS) into two separate fields Date (yyyy-mm-dd) and time (hh:mm:ss)??

Hi Jyotish

Don’t worry i just used your Date script and modified by just swapping date with month and it worked almost close to what i wanted.

Month(timestamp#(@5,'YYYY-MM-DD:hh:mmSmiley FrustratedS')) as Month,

This is how my list boxes look now which is ok

I want to plot a bar graph showing monthly totals counts of 2G occurrences vs 3G occurrences.

For example for each country in the month of Sep count the number of times 2G appeared in the list box vs 3G.

I used Month and country as dimensions, and used Count of Technology but that just counts 2G 3G Unknown all together. I want counts of each technology.

Can you please help again?

Regards

saumyashah90
Valued Contributor

Re: Formatting DATE Field (YYYY-MM-DD :hh:mm:SS) into two separate fields Date (yyyy-mm-dd) and time (hh:mm:ss)??

Hi Ravi,

Use Subfield like:

=

SubField(@5,':',1) as Date;

SubField(@5,':',2)&':'&SubField(@5,':',3)&':'&SubField(@5,':',4)&':'&SubField(@5,':',5)  as Time;

Community Browser