Hi, I have parsed a column that contains a variable number of values, separated by a comma e.g
bob, 22/04/19, dave
dave carol,
bob, fred, 34, james, 723, 1/1/2019
and because i want to process a number of similar files all with varying numbers of items (each separated by commas - I used the 'Text to Columns' tool with a number of columns as 15 (typically 12 is the max number)
however further down the workflow , I am summarizing (Summarize tool) but can't summarize a column that may not exist (but may well exist in the next file being processed) and also using expressions to test the content of the field.
Is there a way to test if a column exists in summarize and expression formulas?
Any help or suggestions appreciated 🙂
Solved! Go to Solution.
Hi @davidoc ,
You can use a dummy record with 15 ( max) columns and union ( auto config by name ) this record and regular data .
The summarize tool will work for any of the 15 cols even if some of the columns are not included in regular file .
Hi @davidoc
The standard way to apply summaries to dynamic columns is to transpose the data, into a pair of name value columns. Apply your summary to the value column grouping by the name. This technique allows you to use the same workflow with different input schema. If the column doesn't exist, it simply doesn't show up in the results. Extra columns get summarized automatically.
Add a RecordID to join back to the original data if required(it usually is). Transpose the data and then apply the summarizing functions to get the results.
Dan