Test that column exists

[davidoc]

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 🙂

[benakesh]

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 .

[danilang · Accepted solution]

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