Transform Queries
Transform Queries in DPL Studio use DPL expressions to manipulate and transform data from existing queries, including: Standard Queries, Aggregate Queries, Transform Queries.
Transform Queries allow you to create new data structures and formats using expressions, functions, and logical operations. The result of a Transform Query is always returned as a table, regardless of the type of the source expression.
Transform Query Results
Scalar Values : If a Transform Query uses or returns a scalar (single) value, the result is displayed as a table containing : One row, One column
Vector Values : If a Transform Query uses or returns a vector (one-dimensional list of values), the result is displayed as : One column, Multiple rows,
Nested Transform Queries : When a Transform Query is used within another Transform Query, it is always treated as a table and returns a table result.The final value of a transform query is always presented as a table, regardless of the dimension of the source expression.
Sample Data
Query1
| Column1 | Column2 |
|---|---|
| a | d |
| b | e |
| c | f |
Query2
| Column1 | Column2 |
|---|---|
| aa | dd |
| bb | ee |
| cc | ff |
Combining Columns from Multiple Queries:
The Select function can be used to combine data from multiple queries into a single table.
Sample Query 1
Select([Query1.Column2], [Query2.Column1])
Result:
| Column1 | Column2 |
|---|---|
| d | aa |
| e | bb |
| f | cc |
Sample Query 2
Select([Query2.Column1], [Query1.Column2], Array(1,2,3))
Result:
| Column1 | Column2 | Column 3 |
|---|---|---|
| aa | d | 1 |
| bb | e | 2 |
| cc | f | 3 |
Sample Query 3
Select([Query1.Column1] + [Query2.Column2])
Result:
| Column1 |
|---|
| add |
| bee |
| eff |
Sample Query 4:
Select([Query1.Column1] as C1, [Query2.Column2] as C2)
Result:
| C1 | C2 |
|---|---|
| a | dd |
| b | ee |
| c | ff |
The as keyword is used to assign custom column names. In this example, the columns are renamed to C1 and C2.
Filtering Rows from Queries
The Filter function is used to return only the rows that satisfy specified conditions.
Sample Query 5
Filter([[Query1]], [Query1.Column1] <> "a")
Result:
| Column1 | Column2 |
|---|---|
| b | e |
| c | f |
Sample Query 6
Filter([[Query1]], ([Query1.Column1] = "a") or ([Query1.Column2] = "f"))
Result:
| Column1 | Column2 |
|---|---|
| a | d |
| c | f |
These examples demonstrate how Transform Queries in DPL Studio can be used to combine, manipulate, and filter data using DPL expressions and functions.