adding a column if not brought in by pivot operation

[Sundaram Sridharan]

I query a database to bring in new data and one of the column values are used as column names in a subsequent pivot operation - I need some of those columns to be always there as my chart options depend on it - but sometimes a key value is not present in a data set and hence that column doesn't get generated. How do I make sure a given number of default column names are generated by the pivot even if the data doesn't contain those values I don't mind a full blank/empty column but I need it there.

[Gaia Paolini]

I have an idea but not sure it would work for you.

Have a dummy dataset with one row of data and all the columns you need.

Always add this dataset to your original one. In the data canvas, select Add Rows, import this dataset, and you can specify to include all the columns from the new data in the new dataset. As a bonus you get an extra column called Origin that you can use to filter out the dummy data.

[Sundaram Sridharan]

I tried this appraoch and ran into an issue. Let's say "myColumn" is the one that I expect from new data but may not materialize - I have this column in a template along with other column names that will always come through. After pivot operation, I add this table using the "add rows" transform. If that column is not in the incoming records, then this column gets added. But if that column is present, then since I haven't picked this column in the "matching column" settings, this gets added as a duplicate column - like "myColumn(2)" which is undesirable.

[Gaia Paolini]

I see, hmmm. In this case, I think the option would be to use an Ironpython Script. Since you would be using  a script anyway, forget the dummy dataset and simply add any column that you don't have in your dataset as a calculated expression (a null value).

 

Is IronPython available to you

[Sundaram Sridharan]

I started going down the ironpython road myself but need help there. Please see the question I posted

 

https://community.tibco.com/questions/adding-blank-column-using-ironpython