Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a simple subroutine:
Sub ListFields(table_name, exclude)
Trace Excluding columns: $(exclude);
FieldNames:
Load Concat(filtered_field_names, ', ') as filtered_field_names_concat
;
Load field_names as filtered_field_names
Where WildMatch(field_names, '$(exclude)') = 0
;
Load FieldName(IterNo(), '$(table_name)') as field_names
Autogenerate 1 While IterNo() <= NoOfFields('$(table_name)');
Let ListFields = Peek('filtered_field_names_concat', 0, 'FieldNames');
End Sub
It takes a preloaded table and lists the field names in a variable ListFields. If you want you can pass an expression to exclude fields using a WildMatch().
It works:
example_table:
Load *
Inline [
column1, column2, exclude1, exclude2, column3
];
Let vColumnExclusion = 'exclude*';
Call ListFields('example_table', '$(vColumnExclusion)')
Trace Listed Columns: $(ListFields);
But I need to do this repeatedly over the same table, with different exclusions. So I use a For..loop (simplified in my code example)
But It doesn't work in a For.. loop.
For i=-1 to 0
Let vColumnExclusion = If($(i), 'column*', 'exclude*');
Call ListFields('example_table', '$(vColumnExclusion)')
Trace $(ListFields);
Next i
vColumnExclusion is correctly assigned and passed to the subroutine as confirms the Trace inside the sub.
But ListFields only every contains the result set of the first exclusion "column*". The second vColumnExclusion is passed but not executed.
What is going on? Any hint, explanation or even solution would be greatly appreciated.
@Kellerassel It seems to be working as expected as it is generating two records for two conditions
There is a reason it is not displaying correct value in trace statement, because of below statement
Let ListFields = Peek('filtered_field_names_concat', 0, 'FieldNames');
When first loop runs, it shows correct value in trace because in FieldNames table there is only one record. In next loop there are two records in FieldNames table, but above Peek statement has static parameter of "0" which displays first record only. Parameter of 0 in peek represents first record of the table.
To show the correct value in Trace you need to change the parameter of the Peek function dynamically for which little bit of code change will be required like below
example_table:
Load *
Inline [
column1, column2, exclude1, exclude2, column3
];
Sub ListFields(table_name, exclude, iteration)
Trace Excluding columns: $(exclude);
FieldNames:
Load Concat(filtered_field_names, ', ') as filtered_field_names_concat
;
Load field_names as filtered_field_names
Where WildMatch(field_names, '$(exclude)') = 0
;
Load FieldName(IterNo(), '$(table_name)') as field_names
Autogenerate 1 While IterNo() <= NoOfFields('$(table_name)');
Let ListFields = Peek('filtered_field_names_concat', $(iteration), 'FieldNames');
End Sub
For i=-1 to 0
Let vColumnExclusion = If($(i), 'column*', 'exclude*');
Call ListFields('example_table', '$(vColumnExclusion)',$(i)+1);
Trace $(ListFields);
Next i
@Kellerassel It seems to be working as expected as it is generating two records for two conditions
There is a reason it is not displaying correct value in trace statement, because of below statement
Let ListFields = Peek('filtered_field_names_concat', 0, 'FieldNames');
When first loop runs, it shows correct value in trace because in FieldNames table there is only one record. In next loop there are two records in FieldNames table, but above Peek statement has static parameter of "0" which displays first record only. Parameter of 0 in peek represents first record of the table.
To show the correct value in Trace you need to change the parameter of the Peek function dynamically for which little bit of code change will be required like below
example_table:
Load *
Inline [
column1, column2, exclude1, exclude2, column3
];
Sub ListFields(table_name, exclude, iteration)
Trace Excluding columns: $(exclude);
FieldNames:
Load Concat(filtered_field_names, ', ') as filtered_field_names_concat
;
Load field_names as filtered_field_names
Where WildMatch(field_names, '$(exclude)') = 0
;
Load FieldName(IterNo(), '$(table_name)') as field_names
Autogenerate 1 While IterNo() <= NoOfFields('$(table_name)');
Let ListFields = Peek('filtered_field_names_concat', $(iteration), 'FieldNames');
End Sub
For i=-1 to 0
Let vColumnExclusion = If($(i), 'column*', 'exclude*');
Call ListFields('example_table', '$(vColumnExclusion)',$(i)+1);
Trace $(ListFields);
Next i
@Kushal_Chawda
Oh for crying out loud. It's so obvious now that you say it. Most of the times when I don't get what's going on in my script it's due to automatic concatenation somewhere. Thank you very much. Solution Accepted.