Querying CDW data

Use variables in Liquid to make your code more extensible and maintainable. Declare a variable using the assign method. For example:

The above code renders into the following code for execution against the database:

Use if/else statements and other control flow tags to change your SQL code dynamically in response to inputs from variables or parameters. In the following example, the query executed against the database differs depending on the value of the car_type variable:

If car_type = 'trucks', the following code is executed:

If car_type = 'cars', the following code is executed:

Loops and other Liquid iteration tags can be used to programmatically generate lists of variables, join statements, columns to select, unions, and other constructs. The query below shows a simple example of a for loop:

The above code joins the draft_picks table to the teams table four times. Each join is assigned a distinct alias (d1 through d4) and a different condition (the round number of the draft pick). The rendered code sent to the database for execution is:

In some cases, you may want the last iteration of the loop to produce a different result than other iterations. For example, when creating a comma-separated list of strings, you may want a comma after every value except the last one. Liquid includes a forloop.last statement for this purpose:

For every iteration of the loop except the last one, forloop.last returns false. Therefore, the value in the unless statement — a comma — is added to your query after every name except the last one.

Typically, for loops cycle through collections of values, such as iterable objects in Python or vectors in R. Liquid does not allow you to create arrays the same way as in most languages (for example, list = ['candy','beans']). To create an array you can iterate over in a for loop, use the split filter on a delimited string and assign the result to a variable. For example:

The above code converts the comma-delimited string 'candy,beans,pizza' to an array and assigns that array to the variable food. The for loop then iterates over each value in the array variable food.

Use {% comment %} and {% endcomment %} tags to instruct Analyst Studio to ignore whatever text or code is written between them.

Parameters allow you to define forms that are configurable by viewers of your Report and which return Liquid objects in your Report’s code. Parameters are a great way to make Reports more extensible, maintainable, and scalable.

Liquid templates can be used when defining custom query headers in data sources connected to your Analyst Studio Workspace. A custom query header is prepended to every query run against that data source and is a great way to increase logging fidelity in your database.