topic Re: Time format in QlikView

Time format

Thu, 08 Oct 2015 04:36:49 GMT

Hi All,

Time should be shown as 1-2AM, 2-3AM similarly till 22-23PM 23-24

Please help me with this.

Thanks,

Keerthi KS

Re: Time format

robert_mika — Thu, 08 Oct 2015 04:46:58 GMT

What is your base data?

Re: Time format

senpradip007 — Thu, 08 Oct 2015 04:49:39 GMT

Can you share sample data?

Re: Time format

Thu, 08 Oct 2015 04:50:03 GMT

Date and Time

2014-12-31 06:23:46.480

2015-01-01 04:34:51.893

2015-01-05 08:33:49.000

Re: Time format

avinashelite — Thu, 08 Oct 2015 05:16:31 GMT

Hi Keerthi,

Please find the attachment for the solution

Re: Time format

sunny_talwar — Thu, 08 Oct 2015 05:24:37 GMT

May be this:

Table:

LOAD *,

Time(Floor(Time, 1/24), 'h') &'-' & Time(Ceil(Time, 1/24), 'h TT') as TimeRange;

LOAD [Date and Time],

Time(Frac([Date and Time])) as Time,

Date(Floor([Date and Time])) as Date;

LOAD * Inline [

Date and Time

2014-12-31 06:23:46.480

2015-01-01 04:34:51.893

2015-01-05 08:33:49.000

];

Output in Table Box:

Re: Time format

Thu, 08 Oct 2015 05:26:15 GMT

Thanks a lot Avinash.

Please send for the below format

6-9AM

9-10AM

10-11AM

11- 12N

12-1PM

1-2PM

2-3PM

3-4PM

4-5PM

5-6PM

6-7PM

7-8PM

8-9PM

9PM -12AM

12-6AM

Re: Time format

sunny_talwar — Thu, 08 Oct 2015 05:32:20 GMT

May be something like this for customized ranges:

Table:

LOAD *,

If(Time <= (6/24), '12-6 AM',

If(Time <= (9/24), '6-9AM',

If(Time <= (21/24), Time(Floor(Time, 1/24), 'h') &'-' & Time(Ceil(Time, 1/24), 'h TT'), '9-12PM'))) as TimeRange;

LOAD [Date and Time],

Time(Frac([Date and Time])) as Time,

Date(Floor([Date and Time])) as Date;

LOAD * Inline [

Date and Time

2014-12-31 06:23:46.480

2015-01-01 04:34:51.893

2015-01-05 08:33:49.000

];

Re: Time format

avinashelite — Thu, 08 Oct 2015 06:32:51 GMT

please find the attachment for the solution

Re: Time format

Thu, 08 Oct 2015 09:58:36 GMT

Hi Sunny,

Please find the attached QVW.

I am not able to get the Time Range.

Its showing but dates are not matching.

Please help me with this.

Regards,

Keerthi KS

Re: Time format

avinashelite — Thu, 08 Oct 2015 10:38:32 GMT

please find the attachment for the solution:

Re: Time format

Thu, 08 Oct 2015 10:43:01 GMT

Thanks Avinash.

Please check with the attached qvw.

Data is not matching with the time interval.

Re: Time format

avinashelite — Thu, 08 Oct 2015 10:47:48 GMT

no , its matching perfectly . Did you check my attachment? based on by solution can quote the example where its not matching

Re: Time format

avinashelite — Thu, 08 Oct 2015 10:48:53 GMT

check this attachment

Re: Time format

Thu, 08 Oct 2015 11:26:44 GMT

Please check my new attachment its not working

Re: Time format

avinashelite — Thu, 08 Oct 2015 12:14:30 GMT

you have created the interval for the "TimeLeadCreatedOn" time field and your comparing the time for some other time field column i.e. "LeadCreatedOn" ....create the interval for the field you required , if you want the interval on " LeadCreatedOn" then create the interval for the same

Re: Time format

Thu, 08 Oct 2015 12:20:04 GMT

Even if i check with TimeLeadCreatedOn its not working.

If i check with 6-9 AM . 10 AM data is also coming

Re: Time format

avinashelite — Thu, 08 Oct 2015 12:21:58 GMT

Are checking the interval for the "TimeLeadCreatedOn " created column only or your trying to check the link for the other field. Can you share the updated app where "TimeLeadCreatedOn " is not matching

Re: Time format

avinashelite — Thu, 08 Oct 2015 12:25:12 GMT

are you checking for the same field which you have created the interval? can you share the updated app where the interval and the time columns are same and still not matching?

Re: Time format

Thu, 08 Oct 2015 12:27:10 GMT

PFA