topic Re: Load script - check if value is inside some range in QlikView

Load script - check if value is inside some range

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I've got a lot of different ranges to check. I don't like spaghetti code so is there some kind of elegant way to check if some value is inside specific range?

example is that I want to check if some telephone number is from specific provider. First I'm checking digits after dialing code than dialing code and last if phone part is inside valid range.

Digits after dialing code	Dialing code	Valid range from	Valid range to
5	014	60000	60999
5	014	62000	63999
5	014	65000	68999
5	014	70000	70999
5	014	74000	81999
5	014	83000	87999

And there are more than 2000 combinations. Is there some easy way to solve this in load script?

Thanks

Re: Load script - check if value is inside some range

MarcoWedel — Tue, 21 Nov 2017 23:36:03 GMT

Can you post your expected result based on your sample data?

thanks

regards

Marco

Re: Load script - check if value is inside some range

Anonymous — Wed, 22 Nov 2017 11:14:07 GMT

Sure

Here is Example set of data

Provider	Dialing code	Digits after dialing code	Valid range from	Valid range to
Telekom Srbija a.d., Beograd	010	6	304000	306999
Telekom Srbija a.d., Beograd	010	6	310000	314999
Telekom Srbija a.d., Beograd	010	6	316000	327999
Telekom Srbija a.d., Beograd	010	6	340000	347999
Telekom Srbija a.d., Beograd	010	6	352000	352999
Telekom Srbija a.d., Beograd	010	6	353000	353999
Telekom Srbija a.d., Beograd	010	6	360000	363999
Telekom Srbija a.d., Beograd	010	6	365000	365999
Telekom Srbija a.d., Beograd	010	6	370000	372999
Telekom Srbija a.d., Beograd	010	6	373000	373999
Telekom Srbija a.d., Beograd	010	6	375000	378999
Telekom Srbija a.d., Beograd	010	6	383000	387999
SERBIA BROADBAND - SRPSKE KABLOVSKE MREŽE	010	6	500000	501999
ORION TELEKOM	010	6	510000	519999
BEOTELNET-ISP	010	6	520000	520999
TELENOR	010	6	540000	540999
TELENOR	010	6	541000	541999
VIP MOBILE	010	6	599000	599999
Telekom Srbija a.d., Beograd	010	6	620000	620999
Telekom Srbija a.d., Beograd	010	7	2100000	2100999
Telekom Srbija a.d., Beograd	010	7	2150000	2150999
Telekom Srbija a.d., Beograd	010	7	2157000	2157999
Telekom Srbija a.d., Beograd	010	7	2310000	2314999

Now if I check phone number I'd like to collect Provider. In case when something is not right I'd like to receive error.

1st 3 digits are Dialing code, then it comes phone part. Example below trows error for 01051250 because phone length is 5 and that is not valid for this dialing code + Valid range

Example:

Phone to check	Provider as result
010383251	Telekom Srbija a.d., Beograd
010501221	SERBIA BROADBAND
010512521	ORION TELEKOM
01051250	Error

So one dialing code can have different length of phone numbers following like 014 --> 5,6 or 7 and for each combination there is a different range of possible numbers.

Dialing code	Digits after dialing code
010	6
010	7
011	7
012	6
012	7
013	6
013	7
014	5
014	6
014	7
015	6
015	7
016	5
016	6
016	7
017	5
017	6
017	7

Any suggestion is appreciated

Thanks

Re: Load script - check if value is inside some range

Mark_Little — Wed, 22 Nov 2017 11:36:04 GMT

HI,

If you have something with the Ranges on you are probably looking at at something like the interval match function.

Mark

Re: Load script - check if value is inside some range

Anonymous — Fri, 24 Nov 2017 09:04:32 GMT

This is it.

Thank you

here is sample code that did it for me...

Provajderi:
LOAD * INLINE [
from, to, Provajder
21000, 29999, Telekom Srbija
46000, 48999, Telekom Srbija
50000, 50999, Telekom Srbija
50000, 59999, Telekom Srbija
51000, 59999, Telekom Srbija
55000, 57999, Telekom Srbija
57000, 59999, Telekom Srbija
60000, 60999, Telekom Srbija
61000, 62999, Telekom Srbija ];

load *

FROM

(qvd);

Left join(Telefoni)
Intervalmatch(Telefon)
LOAD from, to
RESIDENT Provajderi;

Left join (Telefoni)
LOAD * resident Provajderi;
drop table Provajderi;