topic Re: IntervalTable() not working in QlikView

IntervalTable() not working

Anonymous — Mon, 23 Nov 2015 18:13:25 GMT

I have a document that i want to track time parcels are received in quarterly bands, this is simple with power pivot but i have to do it in Qlikview. The issue is its not classifying one of the times its simply ignoring it, I have created a pie chart because i want to show it as a pie chart and clearly one of the slices is random i dont know where it came from. I have looked at the code and it seems its not classifying 08:30 for some reason here is the code and i have attached pictures. If there is a better way please advice me.

Performance:

Load * Inline [

MinTime, MaxTime, Performance

07:45, 08:15, Before 08:15

08:16, 08:30, 08:15 - 08:30

08:31, 08:45, 08:31 - 08:45

08:46, 09:00, 08:46 - 09:00

09:01, 12:00, Beyond 09:00

];

Directory;

LOAD OriginCity,

ArrivalTime

FROM

(ooxml, embedded labels, table is Sheet1);

IntervalBridge:

IntervalMatch(ArrivalTime) LOAD MinTime, MaxTime

Resident Performance;

Re: IntervalTable() not working

swuehl — Mon, 23 Nov 2015 18:22:46 GMT

This could be a rounding issue. The ArrivalTime you load from excel may have a numeric representation little larger than '08:30' as you created in in your Performance table, and smaller than 08:31. Hence it won't match to an existing interval.

Try to round your ArrivalTime values using QV time() and time#() functions, maybe like

Set TimeFormat = 'hh:mm';

Performance:

Load * Inline [

MinTime, MaxTime, Performance

07:45, 08:15, Before 08:15

08:16, 08:30, 08:15 - 08:30

08:31, 08:45, 08:31 - 08:45

08:46, 09:00, 08:46 - 09:00

09:01, 12:00, Beyond 09:00

];

LOAD OriginCity,

Time#(Time(ArrivalTime)) as ArrivalTime

FROM

(ooxml, embedded labels, table is Sheet1);

Re: IntervalTable() not working

sunny_talwar — Mon, 23 Nov 2015 18:23:21 GMT

This may be:

Performance:

Load Time(Evaluate(MinTime)) as MinTime,

Time(Evaluate(MaxTime)) as MaxTime,

Performance

Inline [

MinTime, MaxTime, Performance

465/1440, 495/1440, Before 08:15

496/1440, 510/1440, 08:15 - 08:30

511/1440, 525/1440, 08:31 - 08:45

526/1440, 540/1440, 08:46 - 09:00

541/1440, 720/1440, Beyond 09:00

];

Directory;

LOAD * Inline [

OriginCity, ArrivalTime

Wolverhampton, 08:30

];

IntervalBridge:

IntervalMatch(ArrivalTime)

LOAD MinTime,

MaxTime

Resident Performance;

Re: IntervalTable() not working

Anonymous — Mon, 23 Nov 2015 18:26:27 GMT

forgot to attache the table from excel anyway its as follows

OriginCity	ArrivalTime
Birmingham	08:39
Eastbourne	08:10
Brighton	08:20
Kent	08:15
Edinburgh	08:24
Leicester	08:20
Harlow	08:25
Stevenage	08:24
Wolverhampton	08:30
Morton	08:20
Sherringham	08:13
Southampton	08:38
Norwich	08:28
Leads	08:29
Hull	09:18
Huddersfield	08:41
Newcastle	08:15
Sunderland	08:35
Nothampton	08:37
Bolton	08:14
Liverpool	08:42
Shelford	08:36
Hitchin	08:08
Winchester	08:15

Re: IntervalTable() not working

sunny_talwar — Mon, 23 Nov 2015 18:37:44 GMT

Script:

Performance:

Load Time(Evaluate(MinTime)) as MinTime,

Time(Evaluate(MaxTime)) as MaxTime,

Performance

Inline [

MinTime, MaxTime, Performance

465/1440, 495/1440, Before 08:15

496/1440, 510/1440, 08:15 - 08:30

511/1440, 525/1440, 08:31 - 08:45

526/1440, 540/1440, 08:46 - 09:00

541/1440, 720/1440, Beyond 09:00

];

Directory;

LOAD * Inline [

OriginCity, ArrivalTime

Birmingham, 08:39

Eastbourne, 08:10

Brighton, 08:20

Kent, 08:15

Edinburgh, 08:24

Leicester, 08:20

Harlow, 08:25

Stevenage, 08:24

Wolverhampton, 08:30

Morton, 08:20

Sherringham, 08:13

Southampton, 08:38

Norwich, 08:28

Leads, 08:29

Hull, 09:18

Huddersfield, 08:41

Newcastle, 08:15

Sunderland, 08:35

Nothampton, 08:37

Bolton, 08:14

Liverpool, 08:42

Shelford, 08:36

Hitchin, 08:08

Winchester, 08:15

];

IntervalBridge:

IntervalMatch(ArrivalTime)

LOAD MinTime,

MaxTime

Resident Performance;

Re: IntervalTable() not working

Anonymous — Mon, 23 Nov 2015 18:39:08 GMT

Thanks a lot that worked, the second option of formatting the ArrivalTime - Time#(Time(ArrivalTime)).

One question i want to count the number so that i can have a correct calculation, when i create count(arrivalTime) expression it only counts unique and not all is there a function similar to DAX like Countrows() opposite of CountDistinct()?

Re: IntervalTable() not working

Anonymous — Mon, 23 Nov 2015 18:40:50 GMT

thank you that worked

Re: IntervalTable() not working

swuehl — Mon, 23 Nov 2015 19:03:14 GMT

One question i want to count the number so that i can have a correct calculation, when i create count(arrivalTime) expression it only counts unique and not all is there a function similar to DAX like Countrows() opposite of CountDistinct()?

sorry, I don't understand your question. Count(ArrivalTime) should alread give you a non-distinct count.

Could you describe your issue a little more, best with some sample data?

Re: IntervalTable() not working

Anonymous — Wed, 25 Nov 2015 23:43:32 GMT

Thank you for the reply. The issue is there is bound to be ArrivalTimes that are the same so with the data above that i shared earlier I have decided to make Eastbourne, Kent and Sherringham ArrivalTime the same and changed them to 08:10. and if i want to do the count of ArrivalTimes that belong to the same Bracket, It seems Qlikview does not count all, it only counts distinct which wrong. i have attached the two results before i make the Eastbourne, Kent and Sherringham the same and after i/ made them the same. the result is different. Please refer to the images attached.

Re: IntervalTable() not working

swuehl — Thu, 26 Nov 2015 11:19:08 GMT

ArrivalTime is a key field betwen two tables, right?

You should not do a Count(KeyField), that will return no valid value in general. Count a field in one of the tables that show a value for each row you want to consider. In your case, I think you can do

=Count(OriginCity)

Re: IntervalTable() not working

Anonymous — Thu, 26 Nov 2015 12:24:42 GMT

Hi Swuehi,

Thank you for your reply first thing tanks for clarifying that. My view was not to count non key fields sins they may have null values but not ijn this case since they can not be null so that makes sense. thank you.

Second issue is the solution you gave me earlier in response to my original question did not work. I changed a few arrival times to be bang on the boundary and it gave me wrong chart as before, I changed the following:

Birmingham to 08:30

Leeds to 08:30

Huddersfield to 08:46

Bolton 09:01

Hitchin 08:16

Re: IntervalTable() not working

swuehl — Thu, 26 Nov 2015 12:46:51 GMT

Could you upload your current QVW and the input data source file (Excel)?

Re: IntervalTable() not working

Anonymous — Thu, 26 Nov 2015 13:04:35 GMT

OK i have attached the two files

Re: IntervalTable() not working

Anonymous — Thu, 26 Nov 2015 14:12:48 GMT

Hi SunnyT

I have discovered your solution works well with data loaded Inline. when i load the data from file with Arrival times which are right on the borderline like 08:46, 08:45, 08:16, It fails of which most data will be coming from file not inline. you can try it with the file attached.

Re: IntervalTable() not working

swuehl — Thu, 26 Nov 2015 15:09:37 GMT

Seems to work applying the time#() interpretation also to the inline table values.

I assumed it's not necessary at this place, but I stand corrected.

Re: IntervalTable() not working

swuehl — Thu, 26 Nov 2015 15:20:42 GMT

And if you are wondering why I seem to do unnecessary formatting and intepretations, have a look at

Correct Time Arithmetic | Qlikview Cookbook

Re: IntervalTable() not working

Anonymous — Thu, 26 Nov 2015 22:03:25 GMT

This has worked with all the possible selections I have marked it as the correct answer thank you and thanx for the link i will go through it