topic Dropping Fields not in Data Dictionary in App Development

Dropping Fields not in Data Dictionary

alriveg — Mon, 01 Apr 2024 20:12:28 GMT

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!

Re: Dropping Fields not in Data Dictionary

rwunderlich — Mon, 01 Apr 2024 23:45:36 GMT

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

Re: Dropping Fields not in Data Dictionary

alriveg — Tue, 02 Apr 2024 17:38:45 GMT

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

Re: Dropping Fields not in Data Dictionary

rwunderlich — Wed, 03 Apr 2024 16:03:39 GMT

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/ScriptControlStatements/If.htm

-Rob

Re: Dropping Fields not in Data Dictionary

alriveg — Mon, 08 Apr 2024 20:18:55 GMT

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!