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

# Logic Error

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:

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:

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

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

• ###### Re: Re: Logic Error

Hi

Below is the Snapshot for following cases:

1) Monday - Friday

2) Monday - Saturday

Also, see the Attachment.

• ###### Re: Re: Re: Logic Error

Hi,

I've put something together which solves your problem.

Hope this helps!

Kind regards

Stefan

• ###### Re: Re: Re: Re: Logic Error

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

Regards

Av7eN

• ###### Re: Logic Error

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

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'

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

Causing Problem:

• ###### Re: Re: Re: Re: Logic Error

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

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.

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

Thanks

Av7eN

• ###### Re: Logic Error

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

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

Hi,

you are welcome.

for october data it works?

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

*,

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])

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

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

Resident Final;

Calendar:

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

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

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:

Regards

Av7eN

• ###### Re: Re: Re: Re: Re: Re: Re: Re: Logic Error

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

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.

Regards

Av7eN

• ###### Re: Logic Error

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

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.

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:

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

cases are:

1456391

1456412

1456437

1456458

1456485

See the sample cases Snapshot:

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

Regards

Av7eN

• ###### Re: Logic Error

Sir

I am on the verge of closure  of this logic.

can you help me out on the questions I've asked in the previous post.

Regarda

Av7eN

• ###### Re: Re: Logic Error

Hi,

1) you don't need the calendar to be stored with the fact table, this was only for testing if working days match corresponding week days, so you can drop this table

2) right, I modified the script to fix this

Hope this helps!

Best

Stefan

• ###### Re: Re: Logic Error

Yes,  the Logic is now working Perfectly for all scenarios. No issues left.

This is something i called as incredible stuff.......

Many-2 thanks to you man. You are the Master. Thanks for devoting your valuable time and mind into my issue. It was pleasure working with you. I have learnt many new things from you which I'll remember & keep with me for a Lifetime.

Regards

Av7eN