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

      RelationsToExclude:

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

      AutoGenerate(1);

       

      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

           

               RelationsToExclude:

               SQL SELECT RelationsToExclude FROM ... ;

           

               RelationsToInclude:

               Load * Where not Exists(RelationsToExclude, Relation);

               SQL SELECT * FROM ... ;

           

               Drop Table RelationsToExclude;

           

          HIC

            • 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));

                  Load

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

                            autogenerate 10;

                   

                  HIC

                    • 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.

                           

                          HIC