23 Replies Latest reply: Dec 11, 2014 12:45 PM by AVIRAL NAG RSS

    Logic Error

    AVIRAL NAG

      Hi All

       

      I am doing Calculation of Numbers based on Logic.

      The Correct Calculation are Green in Color and Wrong Calculation is Red in Color.

       

      I am calculation Time Difference between CreationTime & OutTime based on:

       

      The Creation Time & End Time should be considered between StartTime & EndTime.

       

       

      I am explaining 2 scenarios how the Calculation is done:

       

      Case No.1:


      WRONG dURATION.png

       

      It's Creation Time is  25/3/2014 1:11:24 PM

      &   OutTime             is   31/3/2014 7:40:00 PM

       

      If you Reload Attached qvd DataMainData, than you'll see its Working weekdays are different for individual Numbers.

      For some of the cases Working Weekdays are Mon - Fri

      & For some of the cases Working Weekdays are Mon - Sat

       

      It's Working Weekdays are Mon - Fri

       

       

      Also, calculate difference b/w OutTime & CreationTime by considering Start Time & End Time i.e. different individual Numbers

       

      25 = 4:18:36 hrs ( Hours b/w 9:00 to 17:30)   Calculation=(17:30 - 1:11:24 = 4:18:36)

      26 = 8:30 hrs  ( Hours b/w 9:00 to 17:30)   Calculation=(17:30 - 9:00= 8:30:00)

      27 = 8:30 hrs  ( Hours b/w 9:00 to 17:30)   Calculation=(17:30 - 9:00= 8:30:00)

      28 = 8:30 hrs  ( Hours b/w 9:00 to 17:30)  Calculation=(17:30 - 9:00= 8:30:00)

      29 = 0 hrs   ( Saturdayto be excluded since Working Weekdays are Mon - Fri)

      30 = 0 hrs  ( Sunday to be excluded since Working Weekdays are Mon - Fri)

      31 = 8:30 hrs  ( Hours b/w 9:00 to 17:30)  Calculation=(17:30 - 9:00= 8:30:00)

       

      Sum of All = 4:18:36 hrs + 8:30 hrs + 8:30 hrs + 8:30 hrs + 8:30 hrs = 38:18:36 hrs

       

       

      Case No.2:

       

      Right Duration.png

       

      It's Creation Time is  30/3/2014 11:46:39 AM

      &   OutTime             is   5/4/2014 5:00:00 PM

       

      If you Reload Attached qvd DataMainData, than you'll see its Working weekdays are different for individual Numbers.

      For some of the cases Working Weekdays are Mon - Fri

      & For some of the cases Working Weekdays are Mon - Sat

       

       

      It's Working Weekdays are Mon - Sat

       

       

      Also, calculate difference b/w OutTime & CreationTime by considering Start Time & End Time i.e. different individual Numbers

       

      30 = 0 hrs  ( Sunday to be excluded since Working Weekdays are Mon - Sat)

      31 = 8:30 hrs ( Hours b/w 9:30 to 18:00)   Calculation=(18:00 - 9:30 = 8:30:00)

      1  = 8:30 hrs ( Hours b/w 9:30 to 18:00)  Calculation=(18:00 - 9:30 = 8:30:00)

      2  = 8:30 hrs ( Hours b/w 9:30 to 18:00)   Calculation=(18:00 - 9:30 = 8:30:00)

      3  = 8:30 hrs ( Hours b/w 9:30 to 18:00)   Calculation=(18:00 - 9:30 = 8:30:00)

      4 = 8:30 hrs ( Hours b/w 9:30 to 18:00)   Calculation=(18:00 - 9:30 = 8:30:00)

      5  = 7:30 hrs ( Hours b/w 9:30 to 18:00)   Calculation=(5:00 - 9:30 = 7:30:00)


      Sum of All = 8:30 hrs+ 8:30 hrs + 8:30 hrs + 8:30 hrs + 8:30 hrs + 7:30 hrs = 50:00:00 hrs

       

       

      Kindly, someone help. It's a tricky logic.

       

       

      Regards

      Av7eN

        • Re: Logic Error
          Srikanth P

          Do you have any other table have which week are Mon-Sat working days ?

            • Re: Re: Logic Error
              AVIRAL NAG

              Hi

               

              Below is the Snapshot for following cases:

               

              1) Monday - Friday

               

              2) Monday - Saturday


              Mon-Sat.png

               

              Also, see the Attachment.

                • Re: Re: Re: Logic Error
                  Stefan Kunte

                  Hi,

                   

                  I've put something together which solves your problem.

                   

                  Hope this helps!

                   

                  Kind regards

                  Stefan

                    • Re: Re: Re: Re: Logic Error
                      AVIRAL NAG

                      Hi

                       

                      Thanks Stefan.

                       

                      In one case - 1399974 I am getting problem. Please help on this.

                       

                      As per your Logic it's answer is coming as 10:23 but the correct answer should come as 1:00

                      hfghfghghgfh.png

                       

                      Regards

                      Av7eN

                        • Re: Logic Error
                          Stefan Kunte

                          Hi,

                           

                          can't test this as your testdata doesnt have this Ticket, but try modifying the part of the script where all is summed  up like this:

                           

                          NumSum(

                                   pick(WeekDay6Flag + 1,

                                if((WeekDay <= 5 and WeekDay >= 1) and [Cal Date] < EndDay and [Cal Date]> StartDay, WorkingHours),

                                if( (WeekDay <= 6 and WeekDay >= 1) and [Cal Date] < EndDay and [Cal Date]> StartDay, WorkingHours)

                                ) ,

                                pick(WeekDay6Flag + 1, if( (WeekDay <= 5 and WeekDay >= 1) and [Cal Date]= StartDay and [Cal Date] <> EndDay , HoursStartDay2),

                                                        if((WeekDay <= 6 and WeekDay >= 1) and [Cal Date]= StartDay and [Cal Date] <> EndDay, HoursStartDay2 )

                                                                ) ,

                                pick ( WeekDay6Flag +1,

                                    if((WeekDay <= 5 and WeekDay >= 1) and [Cal Date]= EndDay, HoursEndDay2),

                                  if((WeekDay <= 6 and WeekDay >= 1) and [Cal Date]= EndDay, HoursEndDay2)

                                                                        )

                               ) as SumWorkingHours

                           

                          Hope this helps!

                           

                          Best

                          Stefan

                            • Re: Re: Logic Error
                              AVIRAL NAG

                              Sir

                              Your Previous Logic was perfect

                              One Last Question hopefully.

                              The 2 cases '1189675' and '1174767'  I have tested which have Window from Mon-Sun 24*7 so in that case no need to exclude anything from such cases.

                              Some More cases which are causing problem are:

                              '1255249','1473485','1261332','1196672'

                              Wrong Resulttt.png

                              Also, when I am adding my All cases Qvd (Latest_Final_Main_Table.qvd ) into your Logic, than it got hanged. Is this happening due to generated calendar or due to large Records.

                               

                              Kindly help.

                               

                              PFA.

                               

                               

                              Regards

                              Av7eN

                                • Re: Re: Re: Logic Error
                                  AVIRAL NAG

                                  Causing Problem:

                                  Req Res.png

                                    • Re: Re: Re: Re: Logic Error
                                      Stefan Kunte

                                      Hi,

                                       

                                      didn't had the cases in my mind when Tickets are solved on one day, added some code to cover these. (See qvw for this).

                                       

                                      However, the missing two minutes probably result from the enddate of 23:59. Actually there are 59 Seconds missing, so the result shoud be like 47:17:01 but this is not captured by your data.

                                       

                                      Your "big" qvd fails because there are some fields missing in this table you have to remove them from the script.

                                       

                                      Hope this helps!

                                       

                                      Best regards

                                      Stefan

                                        • Re: Re: Re: Re: Re: Logic Error
                                          AVIRAL NAG

                                          Hi Stefan

                                           

                                          Your Logic is working.

                                          But I have 2 issues:

                                           

                                          1) When I am testing with only October Data than the Logic is getting Hanged.

                                          NewewwwwwwwwwwAviraaal.png

                                           

                                           

                                          2) The Case - '1399974' is not coming as per the new Logic.

                                           

                                          Thanks

                                          Av7eN

                                            • Re: Logic Error
                                              Stefan Kunte

                                              Hi,

                                               

                                              what do you mean by logic gets hanged?

                                               

                                              for 2) replace

                                               

                                              if( StartDay = EndDay and OutTime <=  EndTimeOutTime and CreationTime >= StartTimeOutTime, OutTime - CreationTime ,

                                                                                            if( StartDay  = EndDay and OutTime > EndTimeOutTime  and CreationTime  < StartTimeOutTime, EndTimeOutTime- StartTimeOutTime,

                                                                                            if( StartDay = EndDay and OutTime>  EndTimeOutTime and CreationTime >= StartTimeOutTime, EndTimeOutTime- CreationTime,

                                                                                            if( StartDay = EndDay and OutTime<=  EndTimeOutTime and CreationTime < StartTimeOutTime,OutTime- StartTimeOutTime ))))

                                               

                                              (Last Case was not correct)

                                               

                                              Best

                                              Stefan

                                                • Re: Re: Re: Re: Re: Logic Error
                                                  AVIRAL NAG

                                                  Perfect Sir.

                                                  You are very quick. Amazing Stuff.......!!

                                                   

                                                  Sir, Logic gets hanged means. I have a qvd which has large data.

                                                  When I am Replacing that qvd in your logic. Suppose, I have tested it with only October data than the Reload is not processing after Calendar Code and it is forming Synthetic keys. Since, the table was not Dropped there and you've picked All fields there by using Load *,

                                                   

                                                  Regards

                                                  Av7eN

                                                    • Re: Re: Re: Re: Re: Re: Logic Error
                                                      Stefan Kunte

                                                      Hi,

                                                       

                                                      you are welcome.

                                                       

                                                      for october data it works?

                                                       

                                                      Scriptfinish.png

                                                      If you mean your script hangs then you have to wait some time as during script execution the counter not always shows progress when qv is performing resource intensive actions.

                                                       

                                                      Nevertheless, you can use this code (after calculation of hours) to remove synthetic key:

                                                       

                                                       

                                                      Final:

                                                      NoConcatenate

                                                      load

                                                      *,

                                                      StartDay&  '|' &

                                                      EndDay as %IntervallKey,

                                                      SumWorkingHours  as Duration

                                                      Resident Table2;

                                                      drop Fields StartDay, EndDay from Final;

                                                       

                                                      drop Tables Table2, Temp;

                                                       

                                                      left join (Calendar1)

                                                      IntervalMatch ([Cal Date])

                                                      load

                                                      date(floor(num(CreationTime)), 'DD/MM/YYYY') as StartDay,

                                                      date(floor(num(OutTime)), 'DD/MM/YYYY') as EndDay

                                                      Resident Final;

                                                       

                                                      Calendar:

                                                      load*,

                                                       

                                                      StartDay&  '|' &

                                                      EndDay as %IntervallKey

                                                      Resident Calendar1;

                                                       

                                                      drop Fields StartDay, EndDay from Calendar;

                                                       

                                                       

                                                      drop Table Calendar1;

                                                       

                                                      Hope this helps!

                                                       

                                                      Best

                                                      Stefan

                                                        • Re: Re: Re: Re: Re: Re: Re: Logic Error
                                                          AVIRAL NAG

                                                          The Reload is working but now new problem:

                                                           

                                                          I am getting Wrong Result after using October Data.

                                                          I am sorry, you are trying so hard and I am keep on raising questions????

                                                            • Re: Re: Re: Re: Re: Re: Re: Logic Error
                                                              AVIRAL NAG

                                                              When I am using single or few cases with small qvd than I am getting Right Result

                                                              but when I am using all the cases with large qvd than I am getting Wrong Result.

                                                               

                                                              This is happening due to the Synthetic Keys forming at earlier stage of script which is Multiplying the data.

                                                               

                                                              The Problem is here:

                                                               

                                                              NewewwwwwwwwwwAviraaal.png

                                                               

                                                              Regards

                                                              Av7eN

                                                                • Re: Re: Re: Re: Re: Re: Re: Re: Logic Error
                                                                  Stefan Kunte

                                                                  Hi,

                                                                   

                                                                  so finally.... this was because the intervalls in your former posted data were not overlapping, adding a key to intervallmatch is the solution

                                                                   

                                                                  Best

                                                                  Stefan

                                                                    • Re: Re: Re: Re: Re: Re: Re: Logic Error
                                                                      AVIRAL NAG

                                                                      This is Perfect.

                                                                      All Scenarios Results are Perfect except one:

                                                                       

                                                                      The Number (ticket ID) Which has CreationTime and OutTime both falling out of Start Time & End Time are giving wrong Results.

                                                                       

                                                                      AllOK.png

                                                                       

                                                                      Regards

                                                                      Av7eN

                                                                        • Re: Logic Error
                                                                          Stefan Kunte

                                                                           

                                                                          if( StartDay = EndDay and OutTime <=  EndTimeOutTime and CreationTime >= StartTimeOutTime, OutTime - CreationTime ,

                                                                                                                        if( StartDay  = EndDay and OutTime > EndTimeOutTime  and CreationTime  < StartTimeOutTime , EndTimeOutTime- StartTimeOutTime,

                                                                                                                        if( StartDay = EndDay and OutTime>  EndTimeOutTime and CreationTime >= StartTimeOutTime and  CreationTime  <= EndTimeOutTime , EndTimeOutTime- CreationTime,

                                                                                                                        if( StartDay = EndDay and OutTime<=  EndTimeOutTime and CreationTime < StartTimeOutTime,OutTime- StartTimeOutTime,

                                                                                                                        if( StartDay = EndDay and OutTime>  EndTimeOutTime and CreationTime > EndTimeOutTime, 0)))))

                                                                          and dont forget to uncheck surpress zero values in your table...


                                                                          Best

                                                                          Stefan

                                                                            • Re: Logic Error
                                                                              AVIRAL NAG

                                                                              Sir

                                                                               

                                                                              I have checked the data. Logic is working in a Perfect way.

                                                                              I appreciate your effort and also very thankful that you devoted your valuable time on this issue.

                                                                              Final changes to be done:

                                                                               

                                                                               

                                                                               

                                                                              1) Can we make the whole logic to be stored in 1 single qvd. The current model is a Link Model.

                                                                               

                                                                              Data Model into One.png

                                                                              So, can we Make this into one Single qvd.

                                                                               

                                                                               

                                                                              2) Issue:

                                                                              Only those cases calculation is coming  whose creation date & out date are same.

                                                                              See below Snapshot:

                                                                              Both are Same.png

                                                                               

                                                                              The cases are not coming whose creation date & out date are different.

                                                                              cases are:

                                                                              1456391

                                                                              1456412

                                                                              1456437

                                                                              1456458

                                                                              1456485

                                                                               

                                                                              See the sample cases Snapshot:

                                                                              Different Dates.png

                                                                               

                                                                              Earlier these all cases were coming. But after the Logic modification these cases are missing out.

                                                                               

                                                                               

                                                                              Regards

                                                                              Av7eN