How to drop multiple temporary fields in script us... - Qlik Community

Report Inappropriate Content · ‎2012-03-22

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.

Report Inappropriate Content · ‎2012-03-30

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 · ‎2012-03-30

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

Report Inappropriate Content · ‎2012-03-31

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

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

Report Inappropriate Content · ‎2012-04-02

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

johnw · ‎2012-04-02

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.

Report Inappropriate Content · ‎2012-04-03

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

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 · ‎2012-04-03

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

Report Inappropriate Content · ‎2012-04-04

... 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 · ‎2012-04-04

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.

How to drop multiple temporary fields in script using wildcard