Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
share the qvd also so that i reload and check
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
If you have got the answer , mark the correct and helpful answers so that it will be helpful for others
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
Hi Avinash,
I have found the issue. Thanks a lot for your support.
Regards,
Keerthi KS
Hi Keerthi,
Cool , your always welcome.