topic Re: Interval Match in QlikView

Interval Match

Johnnyg248 — Tue, 30 Apr 2019 20:58:54 GMT

I have attached a simple qvw that has a very simple interval match

It works exactly how I would like it to

My only issue is the synthetic keys

I have done some research and I do see where people have posted that for interval match is ok

Unfortunately, my boss is in the synthetic key is unacceptable camp that I assume was drilled home by his course instructor.

Can any one please suggest some ways to get rid of the synthetic key?

I do not want to get rid of the lookup table because this is part of a larger project in which users have multiple list boxes

they use to pick "filter" criteria which results in a straight table chart being populated with the desired result set that

can be exported to excel

Thanks!

Can someone

Re: Interval Match

Vegar — Tue, 30 Apr 2019 21:40:07 GMT

You could try this script code.

PlayerData:
LOAD *
INLINE [
Player, FPTS
Anthony Dixon, 6
CJ Spiller, 12
EJ Manuel, 18
Frank Summers, 1
Fred Jackson, 7
Mike Williams, 4
Robert Woods, 8
Sammy Watkins, 3 ];

FPTSLookup:
LOAD
FPTSMin,
FPTSMax,
FPTSLookupValue,
AutoNumberHash256(FPTSMin, FPTSMax) as %FPTSinterval
INLINE [
FPTSMin, FPTSMax, FPTSLookupValue
0, 4, <5
5, 9, 5-10
10, 14, 10-15
15, 100, >15
];

IntervalMatch:
INTERVALMATCH (FPTS)
LOAD Distinct
FPTSMin,
FPTSMax
RESIDENT FPTSLookup;
LEFT JOIN (IntervalMatch)
LOAD
*,
AutoNumberHash256(FPTSMin, FPTSMax) as %FPTSinterval RESIDENT IntervalMatch;

DROP field FPTSMin, FPTSMax from IntervalMatch;

Re: Interval Match

Qrishna — Tue, 30 Apr 2019 22:41:10 GMT

Pl check attached.

Re: Interval Match

Johnnyg248 — Wed, 01 May 2019 14:03:38 GMT

perfect!

Thanks so much for the comments in the script as well!