7 Replies Latest reply: Dec 6, 2013 8:13 AM by Daniël Bolt RSS

    How to implement BETWEEN function

      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
          Henric Cronström

          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

              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;