topic Re: Time detail not reflecting in QlikView

Time detail not reflecting

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Am used the below logic at script level to get the time format,

Script level: 'Time(Time#(BGCMTM, 'hhmmss'), 'hh:mm:ss') as Reporting_Time'

But am getting an issue like as below(For 8:00:00 and 9:00:00 times were missing but in bakend it is available).

Thanks..

Re: Time detail not reflecting

Chanty4u — Wed, 06 Apr 2016 08:47:46 GMT

try this

TimeStamp(

Alt(

TimeStamp#(Field,'hhmmss'),

TimeStamp#(Field,'hh:mm:ss')

'hh:mm:ss'

) as NewTime

Re: Time detail not reflecting

sunny_talwar — Wed, 06 Apr 2016 08:49:03 GMT

May be try this:

Time(Time#(BGCMTM, 'hmmss'), 'hh:mm:ss') as Reporting_Time

Time(Time#(Num(BGCMTM, '000000'), 'hhmmss'), 'hh:mm:ss') as Reporting_Time

Re: Time detail not reflecting

Chanty4u — Wed, 06 Apr 2016 08:52:36 GMT

sample PFA

LOAD *,TimeStamp(

Alt(

TimeStamp#(Date_Value,'hhmmss'),

TimeStamp#(Date_Value,'hh:mm:ss')

'hh:mm:ss'

) as New_Date_Value;

Load * inline [

Date_Value

231000

231212

231222

231138

231039

];

Re: Time detail not reflecting

nareshthavidishetty — Wed, 06 Apr 2016 08:57:52 GMT

Hi Sunny,

It results as below which not good.

The business starts at 8:00 and ends at 18:00 here we need to capture the traffic count for every hour.

Thanks..

Re: Time detail not reflecting

sunny_talwar — Wed, 06 Apr 2016 09:00:26 GMT

Do you want to round down each time to its nearest hour?

Re: Time detail not reflecting

nareshthavidishetty — Wed, 06 Apr 2016 09:03:44 GMT

Hi Sunny,

My apologies actually i have used the below logic

Time(Time#(BGCMTM, 'hmmss'), 'hh:mm:ss') as Reporting_Time

The below logic is working
Time(Time#(Num(BGCMTM, '000000'), 'hhmmss'), 'hh:mm:ss') as Reporting_Time

Thanks..

Time detail not reflecting

Re: Time detail not reflecting

nareshthavidishetty — Wed, 06 Apr 2016 09:09:36 GMT

Hi,

We can round this each hour by using below logic,

if(right( Time(Time#(Num(BGCMTM, '000000'), 'hhmmss'), 'hh:mm:ss'),2)='00', Time(Time#(Num(BGCMTM, '000000'), 'hhmmss'), 'hh:mm:ss')) as new_Time

Thanks..

Re: Time detail not reflecting

sunny_talwar — Wed, 06 Apr 2016 09:21:28 GMT

That is not rounding, rounding can be done like this may be:

Time(Floor(Time#(Num(BGCMTM, '000000'), 'hhmmss'), (1/(24*60))), 'hh:mm:ss') as New_Time

Re: Time detail not reflecting

nareshthavidishetty — Wed, 06 Apr 2016 09:50:31 GMT

Hi Sunny,

It's not rounding

Time(Floor(Time#(Num(BGCMTM, '000000'), 'hhmmss'), (1/(24*60))), 'hh:mm:ss') as New_Time

Thanks..

Re: Time detail not reflecting

nareshthavidishetty — Wed, 06 Apr 2016 09:52:46 GMT

It's rounding to 7:59:00 t0 8:00:00,

Time(Ceil(Time#(Num(BGCMTM, '000000'), 'hhmmss'), (1/(24*60))), 'hh:mm:ss') as New_Time

Thanks...

Re: Time detail not reflecting

sunny_talwar — Wed, 06 Apr 2016 09:53:44 GMT

It should have been rounding down to the nearest minute, but you want to round down to nearest hour, try this:

Time(Floor(Time#(Num(BGCMTM, '000000'), 'hhmmss'), (1/24)), 'hh:mm:ss') as New_Time

Re: Time detail not reflecting

sunny_talwar — Wed, 06 Apr 2016 09:56:49 GMT

You can try these also:

Time(Round(Time#(Num(BGCMTM, '000000'), 'hhmmss'), (1/24)), 'hh:mm:ss')

Time(Floor(Time#(Num(105900, '000000'), 'hhmmss'), (1/24)), 'hh:mm:ss')

Time(Ceil(Time#(Num(105900, '000000'), 'hhmmss'), (1/24)), 'hh:mm:ss')

Re: Time detail not reflecting

nareshthavidishetty — Wed, 06 Apr 2016 10:12:57 GMT

Thanks Sunny these were really helpful..