topic How to drop multiple temporary fields in script using wildcard in QlikView

How to drop multiple temporary fields in script using wildcard

Thu, 22 Mar 2012 12:02:38 GMT

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

Fri, 30 Mar 2012 12:48:04 GMT

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

johnw — Sat, 31 Mar 2012 00:02:12 GMT

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

Sat, 31 Mar 2012 17:00:30 GMT

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

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

Mon, 02 Apr 2012 14:12:55 GMT

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

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

johnw — Mon, 02 Apr 2012 15:43:53 GMT

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

Tue, 03 Apr 2012 08:35:30 GMT

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

johnw — Tue, 03 Apr 2012 16:55:09 GMT

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

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

Wed, 04 Apr 2012 06:27:27 GMT

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

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

johnw — Wed, 04 Apr 2012 16:28:18 GMT

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.

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

Thu, 05 Apr 2012 06:20:34 GMT

Didn't thought about that in this context - but quite logical now you explain it.

If it were my thread - you would get 10 point 😉 Thanks!