Business Intelligence Tools

Spotfire Transformations — Change column name

  • Have you ever worried that changing a column name might break your file?
  • Have you wondered whether it matters if you change column names with Edit column properties or transformations?   Is there a difference?

This is week 5 of the Transformation series, and this week, I am going to talk about the change column name transformation.  This is one of the simplest tasks you can perform in Spotfire using the steps shown below.  However, in order to make this a more interesting post, I wanted to test out what happens when you change column names, not only with transformations but with column properties.  Is there a difference if you change a column name with a transformation versus changing the column properties?  Furthermore, is there a difference in what happens to other transformations (calculations, pivots, unpivot), insert columns operations and calculations?  I have enough experience with the application that I think I know what happens in each of these cases, but since I have never performed specific testing, I thought this would make for a good blog post.  Furthermore, understanding how Spotfire responds to changes in column names is a critical piece of defining your architecture.  It tells you what you should and shouldn’t do or what you’ll have to redo if you make changes.

How to Change Column Names with Transformations

  1. Go to the Insert men, select Transformations
  2. In the Transformations drop down, select Change column names
  3. Where is says ‘Columns to rename’, move the column name from left to right using the buttons
  4. Where is says ‘Expression’ type in the new name.
    1. NOTE: The function drop down will allow you to apply functions, such as upper or lower, to the column name, but remember that will change the case of the column name, not the case of the column contents.
    2. NOTE: You can only change one at a time.  This is frustrating, and I recommend using the Ideas portal to request this be changed.
  5. Click OK

Now, for the real meat of the post, here are the starting procedures undertaken in order to get a DXP to work with in testing.

Starting Procedures

  1. Add a production data table to a DXP file
  2. Add a well job data table to the DXP file
  3. Add calculated BOE via the Insert menu to the production table
  4. Add calculated BOE via Transformations to the production table
  5. Add the production data again (from Current Analysis) and performed a pivot operation that gives one record per well and sums up GasProd and OilProd.
  6. Inserted columns into the production data table from the well job data table to show if work had occurred on that day.

My production data table looked like this…(this is after the transformations, but it’s there to give you a general idea)

For simplicity in writing this up, I will refer to changing the column name with transformations as Method A and changing the column name with Edit, Column Properties as Method B.

Additional procedures conducted to perform tests —

  1. To test Method A in calculations and transformations, I added a transformation on the GasProd column to change the name from GasProd to Daily Gas Production and then recorded the results of the test.
  2. To test Method B in calculations and transformations, I changed the column name in Edit, Column Properties from OilProd to Daily Oil Production and then recorded the results of the test.
  3. Observed that the production data table had 16, 720 records.  For the columns that I inserted from the well job table, the WORK_TYPE column had the following records, and this is what I compared to after taking the next two steps.
    • Type 1 — 16 rows
    • Type 2 — 2 rows
    • Type 3 — 4 rows
    • Type 4 — 2 rows
    • Type 5 — 1 row
    • Type 6  — 2 rows
    • Empty — 16,693
  4. To test Method A in the join, I added a transformation on the Prod Date column to change the name from Prod Date to Production Date, refreshed the table, and then recorded the results of the test.
  5. To test Method B in the join, I changed the column name in Edit, Column Properties from Well Name to Well Completion Name, refreshed the table, and then recorded the results of the test.

Tests Performed

Test 1 — Test the impact of Method A versus Method B on calculated columns that have been added via the Insert menu

Result from Method A –This calculation no longer worked.  I went into Edit, Column properties for c.BOE (Insert), and the GasProd column was red and not updated with the new column name.  I had to manually update it.

Result from Method B — This calculation was okay.

Test 2 — Test the impact of Method A versus Method B on calculated columns that have been added via Transformations

Result from Method A — This calculation was okay.  The new name was recognized.

Result from Method B — This calculation was okay. The new name was recognized.

Test 3 — Test the impact of Method A versus B on inserted columns (i.e. is the join ok)

Result from Method A — The join appeared to be intact.

Result from Method B — The join appeared to be intact.

NOTE: In Source information, the insert columns operation is performed before the change column name transformation, and I actually expected this to fail because the old column names are shown.  I’m not certain why it doesn’t fail.

Test 4 — Test the impact of Method A versus B on the pivoted version of the data table 

Result from Method A — The GasProd column disappeared from the table, and I had to go through Edit, Data Table properties and update via prompt and enter in the new column name.  The old name was still appearing and was red.

Result from Method B — The table appeared to be incorrect now.  I had to go through Edit, Data Table properties and update via prompt and enter in the new column name.  The old name was still appearing and was red.

 

So what have we learned with this….

  1. Changing column names will always impact your pivots and unpivots.  You will always have to Refresh with prompt and update.
  2. Changing column names will never impact your joins.
  3. Using Transformations to change column names will break pre-existing calculations.
  4. Use Edit Column properties to change column names will not break pre-existing calculations, and it’s faster than using transformations.  Prior to writing this post, I had always been leery of using Edit column properties, because I was afraid joins would be broken, but this shows that is not the case.
  5. Calculated columns always occur last in the build process (see Source info below)
  6. When you change a column name with a transformation, the original name will no longer appear in Column Properties, but when you change a column name from Column Properties, the original name will show up as the External Name.  Also, when you change a column name via a Transformation, the Origin table is no longer shown in Column Properties.  In the screenshot below, I changed Oil Prod to Daily Oil Production using Column properties, and that change is clearly reflected, as opposed to Gas Prod which I changed to Daily Gas Production with a transformation.

 

If you have questions or comments, feel free to contact me at julie@bigmountainanalaytics.com.

Production Data Table Source Info

1. Select File > Add Data Tables…
Source: Data loaded from file
Type: Microsoft Excel Workbooks
Location: S:\Denver\Shared\Julie Schellberg\09 Ruths.ai\02 Data Sets\Prod Volumes Table – 2016.xlsx
Worksheet: 2016 Prod
Last reload: 3/13/2017 5:12 PM

2. Select Insert > Transformations…
Added transformations
Transformation name: Calculate new column
Column name: c.BOE (Transform)
Expression: ([GasProd] / 6) + [OilProd]

3. Select Insert > Columns…
Source: Data table from current analysis
Data table: Well Job Table
Update behavior: Automatic
Last reload: 3/13/2017 5:12 PM
Matching behavior: Manual
Matched columns:
Well Name – Well Name
Prod Date – Job Start
Ignored columns: UWI
Join method: Left single match join
Treat empty values as equal: No

4. Select Insert > Transformations…
Added transformations
Transformation name: Change column names
Columns to rename: GasProd
Expression: Daily Gas Production

5. Select Insert > Transformations…
Added transformations
Transformation name: Change column names
Columns to rename: Well Name
Expression: Well Completion Name

6. Insert > Calculated Column…
Column name: c.BOE (Insert)
Expression: ([Daily Gas Production] / 6) + [Daily Oil Production]

Pivoted Production Data Table

1. Select File > Add Data Tables…
Source: Data table from current analysis
Data table: Prod Volumes Table – 2016
Update behavior: Automatic
Added transformations
Transformation name: Pivot
Row identifiers: Well Name
Value columns and aggregation methods:
Sum(Daily Gas Production)
Sum(OilProd)
Column titles: (None)
Column naming pattern: %V
Transfer columns and aggregation methods: UniqueConcatenate(UWI)
Transfer column naming pattern: %T

Last reload: 3/13/2017 5:12 PM

1 thought on “Spotfire Transformations — Change column name

  1. Pingback: How to Changing Multiple Spotfire Column Names At Once • The Analytics Corner

Leave a Reply

Your email address will not be published. Required fields are marked *