10 Replies Latest reply: Apr 5, 2012 2:20 AM by Thomas Schmutz RSS

    How to drop multiple temporary fields in script using wildcard

      Hi, does anyone know if you can drop multiple fields in Qlikview script in a single statement, e.g. somthing like:

       

      DROP FIELDS z* or

      DROP FIELDS "z*"

       

      The fields are all prefixed by 'z' to let me know that it is a temporary field used in calculations only and not needed in the report developer interface but I don't want to list them all separately.

        • How to drop multiple temporary fields in script using wildcard

          Hi,

           

          unfortunately not exactly waht you looked for, but at least a workaround:

           

          http://qliktips.blogspot.com/2009/10/removing-fields-with-wildcard.html

           

          Hope this helps.

           

          Cheers.

            • Re: How to drop multiple temporary fields in script using wildcard
              John Witherspoon

              Trying to follow that link crashed my browser so I can't see how it was done.  At the risk of posting the same or an inferior solution, this should work (untested):

               

              FOR T = nooftables()-1 TO 0 STEP -1
                  LET vTable=tablename(T);
                  FOR F = 1 TO nooffields('$(vTable)')
                      LET vField=fieldname(F,'$(vTable)');
                      IF  wildmatch('$(vField)','z*') THEN
                          DROP FIELD "$(vField)";
                      ENDIF
                  NEXT
              NEXT

                • Re: How to drop multiple temporary fields in script using wildcard

                  Hi John,

                   

                  sorry about that. Found the blog of Stephen Redmond on the search for the drop with wildcards. The following is the extract of the blog solution:

                   

                  Let i = 1;

                  Do While i <= NoOfFields('MyTable')

                  Trace Getting Field $(i) From MyTable;

                  Let FieldName = FieldName($(i), 'MyTable');

                  Trace FieldName = $(FieldName);

                  Let Command = If('$(FieldName)' Like 'Z*', 'Drop Field $(FieldName);', '');

                  Trace Command = $(Command);

                  $(Command);

                  Let i = $(i) + If('$(Command)' = '', 1, 0);

                  Loop

                  For the reason of reusability i tried to put it in a SUB, so it could be called with table name and wildcard expression:

                   

                  Next step would would have been to try and get independent from table name. Thanks for your superior solution. At least for me it worked great so far. I think it should be possible to put the search expression in a variable vExpression for beeing able to drop all Y* and Z* (etc.) fields?

                   

                   

                  LET vExpression = 'Y*, Z*';

                   

                  FOR T = nooftables()-1 TO 0 STEP -1

                  LET vTable=tablename(T);

                  FOR F = 1 TO nooffields('$(vTable)')

                  TRACE F=$(F);

                  //TRACE F= $(=nooffields('$(vTable)'));            //seems not to be working

                  LET vField=fieldname(F,'$(vTable)');

                  IF  wildmatch('$(vField)','$(vExpression)') THEN

                  DROP FIELD "$(vField)";

                  ENDIF

                  NEXT

                  NEXT

                   

                  Unfortunately the code above doesn't work yet, because there is a problem with the ' in the variable vExpression. Perhaps anyone has hint?

                   

                  Best regards,

                  Tom

                    • Re: How to drop multiple temporary fields in script using wildcard

                      for the sake of completeness:

                       

                      SET vExpression = "'F2*','F4*'";

                       

                          FOR T = nooftables()-1 TO 0 STEP -1

                              LET vTable=tablename(T);

                              FOR F = 1 TO nooffields('$(vTable)')

                                  LET vField=fieldname(F,'$(vTable)');

                                  IF wildmatch('$(vField)',$(vExpression)) THEN

                                      DROP FIELD "$(vField)";

                                  ENDIF

                              NEXT

                          NEXT

                        • Re: How to drop multiple temporary fields in script using wildcard
                          John Witherspoon

                          I'm guessing we could do FOR T = 0 TO nooftables()-1 and avoid the negative step.  I'd grabbed that from some fairly different example I'd done, and I'm thinking maybe it was something that was dropping tables, because I can't think of another reason to have to go in descending order like that.  Maybe there's some other reason, though.

                            • Re: How to drop multiple temporary fields in script using wildcard

                              FOR T = 0 TO nooftables()-1 worked fine for me.

                               

                              But as I noticed there was a problem with the loop counter. It is exactly as Stephen Redmond explained in his blog (mentioned above):

                               

                              Stephon Redmond:

                               

                              Note that you need to use a While statement here not a For loop. This is because once you drop a field, say Field #2, the next field becomes that field number - Field #3 becomes Field #2 - so you will end up skipping fields. The While statement prevents this.

                               

                              The resulting code snippet worked so far for me:

                               

                              SET vExpression = "'tPA0001.*'";

                               

                              FOR vT = 0 TO nooftables()-1

                                  LET vTable        =tablename(vT);

                                  Let vF            = 1;        // initialise variable for next loop

                                  DO While vF <= nooffields('$(vTable)')

                                      LET vField=fieldname(vF,'$(vTable)');

                                      IF wildmatch('$(vField)',$(vExpression)) THEN

                                          DROP FIELD "$(vField)";

                                      ENDIF

                                      Let vF = vF + IF(wildmatch('$(vField)',$(vExpression)),0,1);

                                  LOOP

                              NEXT

                               

                              LET vExpression     = null();    //internal variables -> not shown in frontend

                              LET vTable          = null();    //internal variables -> not shown in frontend

                              LET vField          = null();    //internal variables -> not shown in frontend

                              LET vF              = null();    //internal variables -> not shown in frontend

                              LET vT              = null();    //internal variables -> not shown in frontend

                                • Re: How to drop multiple temporary fields in script using wildcard
                                  John Witherspoon

                                  Thomas Schmutz wrote:

                                   

                                  I noticed there was a problem with the loop counter. It is exactly as Stephen Redmond explained in his blog (mentioned above)...

                                   

                                  Hah!  OK, I guess I got my loops reversed.  Obvious in hindsight.  The tables one can be ascending because we aren't dropping tables.  The field loop has to be descending because we're dropping fields.  A descending for loop seems a lot less complicated than a while loop with a conditional incrementer, assuming it works.  I don't know why I don't just test this code myself, but... 

                                   

                                  FOR vT = 0 TO nooftables()-1
                                      LET vTable = tablename(vT);
                                      FOR vF = nooffields('$(vTable)') TO 1 STEP -1
                                          LET vField = fieldname(vF,'$(vTable)');
                                          IF  wildmatch('$(vField)','tPA0001.*') THEN
                                              DROP FIELD "$(vField)";
                                          ENDIF
                                      NEXT
                                  NEXT