Seperate date&time

[#1]

Hi Guys,

I really need your help as soon as possible..I have a time field and I would like to divide it into date and hour fields...I would like to do it on qlikview and have no idea how to do it...If you give me a hand I really would be appreciated on you..

I have attached the file...

Thanks for all replied my question!

[suresh_rawat · Accepted solution]

Hi Atacan,

If the Date format is fixed, so you can use this:

left(@2,8) as Date,

g - if your date is 201312130802

answer - 20131213

Hour - if you want only hours

mid(@2,9,2) as hour

eg - if your date is 201312130802

answer - 08

Hour - if you want hour and minute

right(@2,4) as time

eg - if your date is 201312130802

answer - 0802

Regards

Suresh Rawat

[Anonymous]

Atacan

Try something like this in your load statement:

     date ( floor ( [DateTimeField] ) )     as DateField ,

     time ( ceil  ( [DateTimeField] ) )     as TimeField ,

     .............

Best Regards,     Bill

[#4]

Hi Suresh,

I have applied it on my report and yes your way is working on it..I just wanna know the systematic of the syntax which is mid(@2,9,2)...Could you explain it briefly how it works?

One more thing, how can I change the time format from HHMM to HH:MM ?

Thanks for replied me..

[#5]

Bill,

It does not work on it..

[suresh_rawat]

Hi Atacan,

Glad that it works for you.

Ist Query:

Mid function working -

Eg: - As per you data - 201312130802 - mid(@2,9,2)

@2 - your Column which consist of Data

9 - It is the position that start from the extreme left which is 2( Ist position)  in your data till the 9th position(from left to right) which is 0 (9th position start from the extreme left).

2 - Means it returns the count of value(two - 08 ) which is defined from the position which is previously mentioned(0 - 9th Position).

eg - if it is 3 then it will return - 080

IInd Query:

Eg - 201312130802

= mid(@2,9,2) & ':' & right(@2,2)

Answer - 08:02

Hope you will understand, if not, you can again raise a question.

Regards

Suresh Rawat

[#7]

Dear Surash,

I am sure that many people benefit from your clear statement with your deep knowledge...And you are really helpful guy for who have started qlikview recently..

Best Regards,

Atacan..