Parameterizing column names

Sometimes it is necessary to dynamically determine column names for computed columns based on the information contained in a variable.

Difficulty

Objective

You want to create several computed columns but know that later you will want the same computations done on different columns. One solution is to change all of the information in your code dealing with the old columns to information about the new columns. However, this can be tedious and time consuming, not to mention that you could miss instances of the old column and not replace the outdated information. Instead you can wrap your code in a block where a variable containing the desired column names is defined. The rest of your code then extracts necessary information based on the information contained in the variable instead of a specific column name.

Solution

<block name="computation" colname="xsales,qty,cost" seg="transid customer">
  <base table="pub.doc.retail.altseg.sales_detail_transid"/>
  <loop with_="columns">
    <outer>
      <base table="pub.doc.retail.altseg.sales_detail_transid"/>
      <columns/>
      <colord cols="label"/>
      <set collist="{str_to_lst(@colname;',')}"/>
      <set collist="'{lst_to_str(@collist;'\' \'')}'"/>
      <sel value="name={@collist}"/>
      <transpose/>
    </outer>
    <inner>
      <letseq names="{@columns}" keys="{pkg_names(@names)}">
        <foreach var="{@colname}" col_label="{@keys}" tally_="@i">
          <willbe name="sum_of_{@var}" label="Sum of 
          {@columns.{@col_label}}" value="g_sum({@seg};;{@var})"/>
        </foreach>
      </letseq>
    </inner>
  </loop>
  <sel value="g_first1({@seg};;)"/>
</block>

Alternate solution

<block name="computation" colname="xsales,qty,cost" seg="transid" 
function="sum">
  <base table="pub.doc.retail.altseg.sales_detail_transid"/>
  <loop with_="columns">
    <outer>
      <base table="pub.doc.retail.altseg.sales_detail_transid"/>
      <columns/>
      <colord cols="label"/>
      <set collist="{str_to_lst(@colname;',')}"/>
      <set collist="'{lst_to_str(@collist;'\' \'')}'"/>
      <sel value="name={@collist}"/>
      <transpose/>
    </outer>
    <inner>
      <letseq names="{@columns}" keys="{pkg_names(@names)}">
        <tabu breaks="{@seg}">
          <foreach var="{@colname}" col_label="{@keys}" tally_="@i">
            <tcol name="{@function}_of_{@var}" label="{@function} of 
            {@columns.{@col_label}}" source="{@var}" fun="{@function}"/>
          </foreach>
        </tabu>
      </letseq>
    </inner>
  </loop>
  <sel value="g_first1({@seg};;)"/>
</block>

Discussion

Changing your code to perform the same operations with different values can be problematic. Especially if the code is lengthy, making a mistake in your editing can cause an error that might be difficult to locate. Additionally, it can be a challenge to make sure each instance of the old information is replaced with the new. Running the same query with different data can be simplified by creating one or more variables in a <block> that hold the values of the information you would like to change in the future.

In this solution two variables are defined, one to store column names, colname, and one to store the segmentation of the data table, seg. The column names contained in the colname variable will be used to create tabulated columns containing the sum of the values of each column. These values are calculated using the g_sum(G;S;O;X) function. The seg variable is passed to the g_function to clarify how the table is segmented. To properly label the computed columns, the labels from the source columns are packaged with each column name in the outer loop. Because the column names entered in colname are not stored in a selection friendly format, you must use str_to_lst(X;D) and lst_to_str(X;D) to change the delimiter to a space and add single quotes around each column name.

Alternatively, you can perform a tabulation instead of creating a computed column. This will give you the added option of changing the function by creating a third variable, function. However, using a g_function is preferred due to the table's segmentation. Performing a tabulation will not work on tables with over 3 billion rows.

Common errors

Unequal length iterators in <foreach>: If you do not transpose your table in the outer loop, you will receive this error.
This is because the package created from the outer loop connects each column name to the last value in the column. Without transposing the table, you only have one column. This then tells the <foreach> operation that there is one value in the keys variable and three values in the colname variable.