topic Re: Seperate date&time in QlikView

Seperate date&time

Mon, 16 Dec 2013 07:50:35 GMT

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!

Re: Seperate date&time

suresh_rawat — Mon, 16 Dec 2013 08:06:15 GMT

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

Re: Seperate date&time

Anonymous — Mon, 16 Dec 2013 08:08:33 GMT

Atacan

Try something like this in your load statement:

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

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

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

Best Regards, Bill

Re: Seperate date&time

Mon, 16 Dec 2013 08:36:45 GMT

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

Re: Seperate date&time

Mon, 16 Dec 2013 08:39:27 GMT

Bill,

It does not work on it..

Re: Seperate date&time

suresh_rawat — Mon, 16 Dec 2013 09:11:47 GMT

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

Re: Seperate date&time

Mon, 16 Dec 2013 09:25:18 GMT

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