Description
The SELECTCOLUMNS function reorders, renames and selects columns from a specified table. The result of the function is a new table that includes only the specified columns.
Usage
SELECTCOLUMNS(Table, "New Name 1", COLUMN_EXPR1, "New Name 2", COLUMN_EXPR2, ...)
The first argument must be a table expression, such as form id or another table function.
Following the table argument, the function accepts pairs of column names and expressions. Each pair results in a column in the resulting table.
Examples
Counting unique beneficiaries across multiple activities
When using the UNION function to combine multiple forms into a single table, the SELECTCOLUMNS function is important because it allows you to reshape forms with different structures into common structure, each with the same column names and types.
For example, you might want to combine a form containing the participants of a training, as well as the recipient of small business loans. Both forms have an information about the date and participant of the activity, but the fields have different names, and the date of the training, for example, is located in the parent training session form, not associated with each participant.
UNION(
SELECTCOLUMNS(participants,
"date", @parent.date,
"beneficiary", participant),
SELECTCOLUMNS(loans,
"date", disbursement_date,
"beneficiary", recipient)) |>
COUNTDISTINCTX(beneficiary)
In the example above, we have to first reshape the two forms, Participants and Loans so that they have the same fields in the same order. For the training participants, the relevant date actually comes from the parent form and is associated with the training. For the Loans, we choose to use the disbursement date.