How to implement BETWEEN function

[#1]

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

[hic]

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

[#3]

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;

[#4]

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

[hic]

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

[#6]

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

[hic]

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

[#8]

Thanks!

I will try your solutions!

Regards!