Dropping Fields not in Data Dictionary

[alriveg]

Hi Everyone,

I'm trying to drop fields that are not found in my Mapping Dictionary. I'm using this script:

Let vFieldCount = NoOfFields('_qvd_name');
For i = 1 to $(vFieldCount)
      Let vFieldName = FieldName($(i), '$(_qvd_name)');
      // Check if the field exists in the mapping dictionary
      If Not WildMatch('$(vFieldName)', '$(DataDictionaryMap.[Standard Field Name])')
      // If not found, drop the field from main table
      Then
          Drop Field '$(vFieldName)';
      End If;
Next;

However, I'm getting this syntax error:

It is getting the correct field name to check (subject Id). The problem seems to lie in the For loop syntax, but I can't find what the error is.

Alternatively, does anyone know of a way to drop fields after renaming them? The renaming is working fine, I just need to remove all the extra fields that are not included in the dictionary and were not renamed. Doing it field by field during the load is not feasible.

Thanks!

[rwunderlich]

You should not use single quotes around the Drop Field fieldname. If you are trying to cover the circumstance where the fieldname contains spaces, use double quotes or square brackets. 

Drop Field "$(vFieldName)";

Note that it can be painfully slow to drop one field at a time. If it's too slow for your needs, modify your loop to collect the fields in a comma separated list and then use a single Drop Field statement. 

-Rob

[alriveg]

The quotes change was correct. Now it's getting the correct value for the field names. However, the error seems to be with the "THEN" statement following the "IF NOT WILDMATCH()".

Let vFieldCount = NoOfFields('_qvd_name');
For i = 1 to $(vFieldCount)
      Let vFieldName = FieldName($(i), '$(_qvd_name)');
      // Check if the field exists in the mapping dictionary
      If Not WildMatch('$(vFieldName)', '$(DataDictionaryMap.[Standard Field Name])')
      // If not found, drop the field from main table
      Then
          LET vDrop = Concatenate($(vFieldName), ',')
      End If;
Next;

I've changed the code to collect the field names in a list, thanks for the suggestion. It's the "THEN" statement that seems to be causing the error. Any suggestions?

Thanks,
Alex

[rwunderlich · Accepted solution]

The script syntax for "Control Statements" requires the statement to be on one line. "Then" need to be on the same line as "If" 

If Not WildMatch('$(vFieldName)', '$(DataDictionaryMap.[Standard Field Name])') Then

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptControlS...

-Rob

[alriveg]

That fixed the error, thanks. The problem I'm still having though is that the dropped fields still appear in the data model, despite being dropped.

That said, I'll mark this as a solution. It did help a lot with the syntax error. Thanks for the help!