Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
alriveg
Contributor II
Contributor II

Dropping Fields not in Data Dictionary

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:

error.png

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!

Labels (1)
1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

4 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor II
Contributor II
Author

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()".

alriveg_3-1712073498358.png

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor II
Contributor II
Author

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!