Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

10 Replies
Not applicable
Author

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.

johnw
Champion III
Champion III

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

Not applicable
Author

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

Not applicable
Author

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

johnw
Champion III
Champion III

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.

Not applicable
Author

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

johnw
Champion III
Champion III

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

Not applicable
Author

... it has already been tested 😉

Works fine and is definitely shorter and better to understand than the while loop. Just for my understanding:  what for are the double quotes in the drop statement? Do they have a special meaning or are they obsolete?

johnw
Champion III
Champion III

If you have field names with whitespace in them, like This Field, the $(vField) substitutes it in literally rather than doing any interpretation, so the line of code you'd actually be executing would would end up like this if you didn't have the quotes:

DROP FIELD This Field;

And that should be a syntax error.  So it needs to either be in double quotes or brackets, depending on your personal preference for field names.  If you name your fields like ThisField, you won't need the quotes/brackets, but I'd still include them as an extra measure of safety.  And I personally always use spaces in my real applications, as I want my field names to be as friendly as possible to my users.

Edit:  Unless I'm wrong.  Again, untested.