16 Replies Latest reply: Nov 6, 2015 11:01 AM by Sunny Talwar RSS

    Match fct

    Silvia Cicoira

      Hi all,

       

      I have a problem with my QlikView project: I have to do a match function between a field and a variable, but it does not seem to work fine (in this way it gives as error "Field not found"

       

       

       

      FOR i = 0 to  $(noRows) - 1
      LET BU = peek('BU_file', $(i));

      NoConcatenate
      StagingBU:
      load *,
      Resident Staging
      where match(BU_file, $(BU));

      next i

       

       

      I have also tried to do:

       

      FOR i = 0 to  $(noRows) - 1

       

      LET BU = FieldValue(peek('BU_file', $(i)),$(i));

       

       

       

      load *,
      '$(BU)'
      as Field1
      Resident Staging
      where match(BU_file, Field1);

      next i ;

       

      but is still not working.

       

      Thanks!

        • Re: Match fct
          Sunny Talwar

          Give this a try:

           

          FOR i = 0 to  $(noRows) - 1
          LET BU = Chr(39) & Peek('BU_file', $(i)) & Chr(39);

          NoConcatenate
          StagingBU:
          load *,
          Resident Staging
          where match(BU_file, $(BU));

          NEXT
          i

            • Re: Match fct
              Silvia Cicoira

              Thank you for your answer,

              with your script the loop takes a correct value with the first play (i=0) but then BU takes value " ' ' "

              Cattura.JPG

                • Re: Match fct
                  Sunny Talwar

                  Try this:

                   

                  Temp:

                  LOAD Concat(BU_file, '|') as List

                  Resident Staging;

                   

                  LET vList = Chr(39) & Peek('List') & Chr(39);

                  DROP Table Temp;

                   

                  FOR i = 0 to  $(noRows) - 1
                  LET BU = Chr(39) & SubField($(vList), '|', $(i)) & Chr(39);

                  NoConcatenate
                  StagingBU:
                  load *,
                  Resident Staging
                  where match(BU_file, $(BU));

                  NEXT
                  i

                    • Re: Match fct
                      Silvia Cicoira

                      I need again your help, if I try to do this, it creates all the different QVD with correct names but it takes only records where BU_file = APP (instead it gives all the BUs names to QVDs)

                       

                       

                      Temp:

                      LOAD Concat(BU_file, '|') as List
                      Resident Stag_BU;



                      LET vList = Chr(39) & Peek('List') & Chr(39);
                      DROP Table Temp;



                      FOR i = 1 to  $(noRows)
                      LET BU = Chr(39) & SubField($(vList), '|', $(i)) & Chr(39);


                      NoConcatenate
                      StagingBU:
                      load *
                      Resident Staging
                      where match(BU_file, $(BU));

                      store StagingBU into $(path_qvd)Staging_$(BU)_$(ANNO)_$(MESE).qvd (QVD);

                      NEXT i

                        • Re: Match fct
                          Sunny Talwar

                          Try adding Distinct to the Concat function:

                           

                          Temp:
                          LOAD Concat(DISTINCT BU_file, '|') as List
                          Resident Stag_BU;

                          LET vList = Chr(39) & Peek('List') & Chr(39);
                          DROP Table Temp;

                          FOR i = 1 to  $(noRows)
                          LET BU = Chr(39) & SubField($(vList), '|', $(i)) & Chr(39);

                          NoConcatenate
                          StagingBU:
                          load *
                          Resident Staging
                          where match(BU_file, $(BU));

                          store StagingBU into $(path_qvd)Staging_$(BU)_$(ANNO)_$(MESE).qvd (QVD);

                          NEXT
                          i

                  • Re: Match fct
                    Srikanth P

                    Match is a string function and so the second variable  in Match function also need string. So try like below:

                     

                    Match(FieldName,'$(varibale)')

                    • Re: Match fct
                      Silvia Cicoira

                      Hi, I found another problem with this script:

                      it seems not to work with Qlik Sense, the variables take the values but the "store" does not work with variables.

                      It only creates a QVD "Staging_$(BU)_$(ANNO)_$(MESE).qvd"