topic Re: How to implement BETWEEN function in QlikView

How to implement BETWEEN function

Wed, 04 Dec 2013 12:33:12 GMT

Hi,

I've got two tables and want to join them with a complex between join. In SQL it's the following join:

TABLE1.rubr_nr between TABLE2.rubr_nr1 and TABLE2.rubr_nr2

and

TABLE1.rek_nr between TABLE2.rek_nr1 and TABLE2.rek_nr2

and

TABLE1.ksrt_nr between TABLE2.ksrt_nr1 and TABLE2.ksrt_nr2

and

TABLE1.kplt_nr between TABLE2.kplt_nr1 and TABLE2.kplt_nr2

and

TABLE1.hrek_nr between TABLE2.hrek_nr1 and TABLE2.hrek_nr2

and

TABLE1.hksrt_nr between TABLE2.hksrt_nr1 and TABLE2.hksrt_nr2

and

TABLE1.hkplt_nr between TABLE2.hkplt_nr1 and TABLE2.hkplt_nr2

and

TABLE1.bedryf_nr = TABLE2.bedryf_nr

How can i implement this in QV?

Regards,

Daniël

Re: How to implement BETWEEN function

hic — Wed, 04 Dec 2013 12:39:19 GMT

You need to use Intervalmatch (http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch) in seven steps, and then concatenate the results from each step. That is, if you want it all in one table...

HIC

Re: How to implement BETWEEN function

Wed, 04 Dec 2013 13:14:55 GMT

Hello Henric,

Thanks for your reply.

I've tried the tutorial at your blog.

Script below, is this the right way to do this?

Grootboek_stam:

LOAD rubr_nr as Grootboek_stam_Rubriek_nummer,

rek_nr as Grootboek_stam_Rekening_nummer,

ksrt_nr as Grootboek_stam_Kostensoort_nummer,

kplt_nr as Grootboek_stam_Kostenplaats_nummer,

hrek_nr as Grootboek_stam_Hoofdrekening_nummer,

hksrt_nr as Grootboek_stam_Hoofdkostensoort_nummer,

hkplt_nr as Grootboek_stam_Hoofdkostenplaats_nummer,

gbrek_veld as Grootboek_stam_Grootboek_nummer,

bedryf_nr as Grootboek_stam_Bedrijfs_nummer

FROM

z:\Ontwikkel\04_QVD_bron\QVD\BI_Tobias_data_gbrek.qvd

(qvd);

BGGRB:

LOAD rubr_nr2,

rubr_nr1,

rek_nr2,

rek_nr1,

rek_inter,

ksrt_nr2,

ksrt_nr1,

kplt_nr2,

kplt_nr1,

hrek_nr2,

hrek_nr1,

hksrt_nr2,

hksrt_nr1,

hkplt_nr2,

hkplt_nr1,

bggrb_id,

bgdeelcomp_srt_rek,

bgdeelcomp_oms,

bgcom_id,

bedryf_nr

FROM

Z:\Ontwikkel\04_QVD_bron\QVD\BI_Tobias_data_bggrb.qvd

(qvd);

LEFT JOIN (Grootboek_stam)

IntervalMatch (Grootboek_stam_Rubriek_nummer) LOAD rubr_nr2, rubr_nr1 RESIDENT BGGRB;

LEFT JOIN (Grootboek_stam)

IntervalMatch (Grootboek_stam_Rekening_nummer) LOAD rek_nr2, rek_nr1 RESIDENT BGGRB;

LEFT JOIN (Grootboek_stam)

IntervalMatch (Grootboek_stam_Kostensoort_nummer) LOAD ksrt_nr2, ksrt_nr1 RESIDENT BGGRB;

DROP Table BGGRB;

Re: How to implement BETWEEN function

Wed, 04 Dec 2013 13:17:03 GMT

This script isn't right. When running this script the number of records isn't right. it goes from 15.0000 records to millions....

Re: How to implement BETWEEN function

hic — Wed, 04 Dec 2013 13:48:57 GMT

It's all the Joins that create duplicates.

No, I suggest an alternative approach.

First Join the two tables using intervalmatch on bedryf_nr and one of the interval fields only. Then run a 2nd pass through this table using a where clause that picks out the right records.

TempResult:

Load * From Grootboek_stam;

Left Join

IntervalMatch (rubr_nr, bedryf_nr)

Load distinct rubr_nr1, rubr_nr2, bedryf_nr From BGGRB;

Left Join

Load * From BGGRB;

Result:

Noconcatenate Load * Resident TempResult

Where rek_nr1 < rek_nr and rek_nr <= rek_nr2

and ksrt_nr1 < ksrt_nr and ksrt_nr <= ksrt_nr2

and ... ;

Drop Table TempResult ;

HIC

Re: Re: How to implement BETWEEN function

Fri, 06 Dec 2013 11:00:55 GMT

Dear Henric,

Thanks for your reply. I tried and tried and tried... But i can't get this done.

Could you give me more tips?

I've attached the QVW and the QVD files, maybe you can check the script.

I need to add the 'bgcom_id' to the' 'Grootboek_stam' table with the complex between join.

Thanks in advance!!!

Daniël

Re: Re: Re: How to implement BETWEEN function

hic — Fri, 06 Dec 2013 13:11:12 GMT

I have two solutions for you. The first one is faster, but uses more memory. The second uses a while loop and a peek(), a method that I have described in IntervalMatch and Slowly Changing Dimensions .

Both solutions generate 64500 records - of the initial 143215 - so there may be a problem with the data, e.g. that there is a key value that doesn't have a corresponding interval. (So that the inner join removes these entries.)

HIC

Re: Re: Re: How to implement BETWEEN function

Fri, 06 Dec 2013 13:13:24 GMT

Thanks!

I will try your solutions!

Regards!