Reference or adjust fields from Transpose operator

Question:

How do I adjust the order or reference the fields that are created by the 'Transpose' operator?

Answer:

The 'transpose' operator dynamically creates fields based on the data it reads. This means that in order to reference any of these fields that 'transpose' creates you need to specify the field names in the 'transpose' operation.

Here is an example where the 'transpose' operation is specifying the order of the fields it creates since they are dates:

_sourceCategory=HttpServers 
| json auto "code.a" as code 
| timeslice 1d 
| formatDate(_timeslice, "MM/dd HH") as period 
| count by code, period 
| transpose row code column period as (%"11/17 00", %"11/18 00", %"11/19 00", %"11/20 00", %"11/21 00", %"11/22 00", %"11/23 00")

When the fields are specified in the transpose operation you can reference them to use with other operators. Here is an example that calculates a 'rate' of change based on two fields, 'pageview' and 'newsignup' created from the 'transpose' operation:

_sourceCategory=HttpServers
| keyvalue auto 
| count_distinct(id) by message, tests
| transpose row tests column message as pageview, newsignup
| newsignup / pageview as rate

Note: Results can be influenced in three ways:

1. By using a comma-separated list of variable names (such as "a, b"), only the specified output fields appear in the output table.
2. By using a comma-separated list of variable names, followed by a comma and a star (such as "a, b,*"), the specified output fields appear in the output table, followed by dynamic fields.
3. By including a single star ("*") all dynamic fields appear in the output. Use this option when you want to add all your fields to the resulting table and when you need to use those fields for any other operator after transpose.

As a reminder, if a field name contains a special character (such as -) the character must be quoted in %"", as in %"test-zz-1". Because column names computed from data tend to include special characters, this is especially important to keep in mind when using a transpose operator.