5 Replies Latest reply: Feb 19, 2013 7:07 AM by Henric Cronström RSS

    Exclude a list of relations

    Roberto Postma

      Hi community,


      A scripting question:

      In our loadscript we have some code like


      SET FinancialRelations  = '123,14413,2417,2342340,24444566,5555,8888,9999';


      The idea is that the (comma separated) relationnumbers listed in that variable are excluded in some loadstatements (like when loading the relations table).


      The current implementation is

      where match(relationNumber,$(FinancialRelations  ))=0

      >>I think this can be risky, for eg when relation 100 must be excluded and relation 1 and 10 exists...  I think that then relation 1 and 10 are excluded (and should not be).


      What is the easiest way to exclude those relations?


      in SQL I would just write

      select * FROM relations where relationNumber not in (select number from RelationsToExclude)


      I know I can load the RelationsToExclude in an inline table with this script


      load Num(SubField('$(BoekhoudkundigeRelaties)',',')) as RelationsToExclude



      Andd probably the easiest way is to use where not exists.... But I wonder if there is no easier way... Since when using not exists... I need the field relationsToExclude also in my relationsTable which I do not need (and I need an additional resident load...)


      Or am I just making things to complicated and is there an easier way?

        • Re: Exclude a list of relations
          Henric Cronström

          A comparison with Match() will work fine.


               Where not Match( relationNumber, $(FinancialRelations));


          But I would probably use a Where not Exists() instead - since you probably have the numbers to exclude in a table already. Hence



               SQL SELECT RelationsToExclude FROM ... ;



               Load * Where not Exists(RelationsToExclude, Relation);

               SQL SELECT * FROM ... ;


               Drop Table RelationsToExclude;



            • Re: Exclude a list of relations
              Roberto Postma

              2 things:

              1: I didn't know that where not exists() accepts more than 1 parameter (and therefore I thought that my relations table had to have the "relationsToExclude" column)... So this is something I learn (and will test asap). Thanks!


              2: I don't understand why the comparison with match will work fine...

              • $(FinancialRelations) is one large string with commaseparated relationnumbers...
              • when this string contains the value 100 ( so relation 100 must be excluded)
              • and my relation table contains relation number 1 and 10... then I assume that Where not Match( relationNumber, $(FinancialRelations)); also excludes 1 and 10...


              Or am I mistaking?

                • Re: Exclude a list of relations
                  Henric Cronström

                  It will only exclude the listed values and no other. Try the following and you'll see:


                  Set FinancialRelations   = 100, 10000 ;

                  Load * Where not Match( relationNumber, $(FinancialRelations));


                            '1'&repeat('0',recno()-1) as relationNumber

                            autogenerate 10;



                    • Re: Exclude a list of relations
                      Roberto Postma

                      There is no other answer possible than You're absolutely right!


                      Thanks for your answer and support!


                      Apparently Qlikview doesn't treat the variable FinancialRelations as a string (...).


                      Even when I enclose the relations with singlequotes (like below), it still works like I want (it excludes the 100 and 10.000).


                      Set FinancialRelations   = '100, 10000';


                      Good to know!

                        • Re: Exclude a list of relations
                          Henric Cronström

                          Whether you have single quotes or not, doesn't matter. The variable FinancialRelations will in both cases have a value equal to a string: '100, 10000'.


                          The magic is in the dollar-expansion. Right before execution of a statement, QlikView will exchange all $(variable) -constructions with the variable value. I.e.

                               Load * Where not Match( relationNumber, $(FinancialRelations));

                          will be exchanged with

                               Load * Where not Match( relationNumber, 100, 10000);


                          And when this statement is executed, the field relationNumber will be compared with 100 and 10000.