Write a dynamic formula when a column exist or even when column does not exist.

[Saravanan13]

Hello,

I am looking to validate if the fields in pdf are updated  correctly, I am getting the fields in PDF as excel extract with multiple columns. Using the excel extract I am performing the validations.

Only issue is the column name "Citizenship country" will only be added to extract when there is a value in PDF.

In the below case there is a data "China" is updated in section "Citizenship country" in PDF. Hence the column is reflecting in the excel extract. If there is no data updated in pdf for "Citizenship country" then the column will not exist in the excel extract. I need use the data in "Citizenship country" for various validations. 

Is there a way whenever the column does not exist in the extract then column name should be added to the extract and the value should be reflecting as null and whenever the column exists in the extract then the column should have the extracted value.

When the field citizenship country is filled in the PDF the extract will be as shown below

Tax country	Citizenship country	Residence address
China	China	123 ABC

When the field citizenship country is not filled in the PDF then the output should be as shown below.

Tax country	Citizenship country	Residence address
China		123 ABC

[SPetrie]

The easiest method I have found is to use a Text Input tool and a union.

In the Text input, you create a column named Citizenship country but do not add any rows to it.

Union that to the main data flow where appropriate. If there column exists, nothing will be added. If there column doesnt exist, it will add it with null values.