Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
Atacan
Try something like this in your load statement:
date ( floor ( [DateTimeField] ) ) as DateField ,
time ( ceil ( [DateTimeField] ) ) as TimeField ,
.............
Best Regards, Bill
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..
Bill,
It does not work on it..
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
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..