Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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
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
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
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