topic Re: Logic Error in QlikView

Logic Error

aveeeeeee7en — Mon, 27 Oct 2014 13:28:05 GMT

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

Mon, 27 Oct 2014 14:25:47 GMT

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

Re: Re: Logic Error

aveeeeeee7en — Mon, 27 Oct 2014 16:10:23 GMT

Below is the Snapshot for following cases:

1) Monday - Friday

2) Monday - Saturday

Also, see the Attachment.

Re: Re: Re: Logic Error

Anonymous — Tue, 28 Oct 2014 21:32:33 GMT

Hi,

I've put something together which solves your problem.

Hope this helps!

Kind regards

Stefan

Re: Re: Re: Re: Logic Error

aveeeeeee7en — Wed, 29 Oct 2014 18:46:12 GMT

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

Anonymous — Wed, 29 Oct 2014 19:42:12 GMT

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

aveeeeeee7en — Wed, 29 Oct 2014 20:44:50 GMT

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

aveeeeeee7en — Thu, 30 Oct 2014 11:32:21 GMT

Causing Problem:

Re: Re: Re: Re: Logic Error

Anonymous — Thu, 30 Oct 2014 18:04:37 GMT

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

aveeeeeee7en — Fri, 31 Oct 2014 08:17:47 GMT

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

Anonymous — Fri, 31 Oct 2014 08:47:04 GMT

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

aveeeeeee7en — Fri, 31 Oct 2014 08:56:55 GMT

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

Anonymous — Fri, 31 Oct 2014 09:44:00 GMT

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

Re: Re: Re: Re: Re: Re: Re: Logic Error

aveeeeeee7en — Fri, 31 Oct 2014 09:56:51 GMT

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

aveeeeeee7en — Fri, 31 Oct 2014 10:02:38 GMT

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

Anonymous — Fri, 31 Oct 2014 11:11:56 GMT

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

aveeeeeee7en — Fri, 31 Oct 2014 11:18:26 GMT

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

Anonymous — Fri, 31 Oct 2014 11:36:20 GMT

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

aveeeeeee7en — Sat, 01 Nov 2014 07:52:39 GMT

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

aveeeeeee7en — Sun, 02 Nov 2014 06:13:58 GMT

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