Time format

[#1]

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

[avinashelite]

share the qvd also so that i reload and check

[avinashelite]

Hi Keerthi,

Your comparing illogically,  see you have created the interval in LeadCalender table and now your trying to compare the time column in Lead table, this will work only if you link both the tables by TimeLeadCreatedOn column.

In your case its getting link via LeadEng_TakenOn column so it showing the data related to this in the other table.

The logic is perfectly working fine, see the second image. you creating the LeadTime based on the TimeLeadCreated column and its showing properly 

[avinashelite]

If you have got the answer , mark the correct and helpful answers so that it will be helpful for others

[MarcoWedel]

Hi,

using Sunny's nested if approach and moving the interval fields to your lead table (because your calendar link does not include time information) another solution could be:

LOAD *,
    Dual(Hour(TimeLeadCreatedOn)&'-'&(Hour(TimeLeadCreatedOn)+1)&Time(TimeLeadCreatedOn,'TT'),Hour(TimeLeadCreatedOn)) as Leadinterval,
    If(Hour(TimeLeadCreatedOn)<=5,Dual('12AM-6AM',0),
        If(Hour(TimeLeadCreatedOn)<=8,Dual('6AM-9AM',6),
          If(Hour(TimeLeadCreatedOn)<=20,Dual(Time(TimeLeadCreatedOn,'hTT')&'-'&Time(TimeLeadCreatedOn+'01:00:00','hTT'),Hour(TimeLeadCreatedOn)),
              Dual('9PM-12AM',21)    ))) as Leadinterval2;

hope this helps

regards

Marco

[#6]

Hi Avinash,

I have found the issue. Thanks a lot for your support.

Regards,

Keerthi KS

[avinashelite]

Hi Keerthi,

Cool , your always welcome.