And you don't know the field names upfront?
Maybe there are better solutions to what you are trying to achieve at the end, for creating a filtered list of field names to load, you can try something like
// Only a dummy table to have something to play with TABLE: LOAD * INLINE [ Dimension, Metric 1, Metric 2 1, 1, 10 2, 2, 9 3, 3, 8 4, 4, 7 5, 5, 6 6, 6, 5 7, 7, 4 8, 8, 3 9, 9, 2 10, 10, 1 ]; // Create a field with all field names FIELDS: CROSSTABLE (FieldName, Value) LOAD 1 as Dummy, * Resident TABLE where RecNo() =1; DROP FIELDS Dummy, Value; // Create a filtered list of the field names, embedded in [..] to handle spaces etc. CONCAT: LOAD '[' & CONCAT(If (FieldName Like 'Metric*', FieldName),'], [') & ']' as FilteredFields RESIDENT FIELDS; // Create a variable from that filtered table value Let vFilteredFields = Peek('FilteredFields',0,'CONCAT'); // Finally load your filtered table from source, instead of a resident TABLE LOAD, you can load your data directly FROM your source here and in the FIELDS table LOAD $(vFilteredFields) RESIDENT TABLE; //Clean up DROP TABLE TABLE, FIELDS, CONCAT;
I have an additional question about your script.
Your suggestion is a smart solution and it works. So thanks for that.
But I need a little more complex add-on because I like to filter the fieldnames on their text 'format' instead of the text itself.
A simplified example of the targeted fieldnames look like these:
I would like to filter on the first 6 characters of the fieldname, where:
the first character is a letter,
the 2nd, 3rd, 5th and 6th is a number
the 3rd is a 'period'
Is it possible to add this to the filter commands your wrote on row 29?