This requires Admin or Data Admin privileges for implementation.
Please Note: If you do not have the correct privileges to access Data Hub you will receive the following upon your login attempt. If you get this message and think it is an error please see your organization's iDashboards Administrator for assistance as Support is unable to give you access.
There is a hidden ETL Job Variable named $rows that is an array of rows that can be used when creating job variables.
Q: Why is it hidden?
A: It is complicated 😂
Background:
When using the Data Hub for ETL jobs, there is a task called Create Job Variables. Job variables are available within the job to all subsequent tasks that occur later along the same path in the job execution. Job variables are available via the ${jv} macro and can be referenced directly within expressions in other tasks. When flow paths contain job variables, the line will appear with a white centerline. There are three primary types of variables:
- Fixed Value
- Single Column Statistic
- An Expression
An Expression offers a few built-in options like being able to return a single value from the first ($first) or last ($last) rows within a column. $first and $last are in the menu options of defining the variable. However, the $rows variable is not displayed in any of the menus, as it is hidden.
Example Data (sorted via THE_ID):
THE_ID | THE_NAME | THE_ORDER |
3 | Joe | ABC_18 |
4 | Sue | ABC_56 |
5 | Tina | DEF_03 |
6 | Joe | XYZ_91 |
7 | Sue | GHI_78 |
Return a single value:
In the basic use case for $rows, it is easily possible to return a single value from a specific row within a column.
- $rows[i]["<column name>"]
- Where i is the row number (starting at 0)
Example:
$rows[3]["THE_ID"]
- result: 6
Example:
$rows[2]["THE_NAME"]
- result: Tina
Return multiple values:
Using additional custom JavaScript, it is possible to achieve the following multi-value results. This means each element of $rows is a Javascript object like this:
{column_1:<value_1>,
column_2:<value_2>
…
Column_n:<value_n>}
Example:
$rows[1]["THE_NAME"] + $rows[1]["THE_ID"]
- results: Sue4
Example:
$rows[1]["THE_NAME"] + " is ID:" + $rows[1]["THE_ID"]
- results: Sue is ID:4
Example:
var Looper= "";
for(var i = 0; i < $rows.length; i++) {
var row = $rows[i];
if(i > 0) {
Looper = Looper + ",";
}
Looper = Looper + row["THE_ORDER"];
}
- result: ABC_18,ABC_56,DEF_03,XYZ_91,GHI_78
- At this time, a comma-separated list cannot be used for filter criteria
Example:
var Looper= "";
for(var i = 0; i < $rows.length; i++) {
var row = $rows[i];
if(i > 0) {
Looper = Looper + ",";
}
Looper = Looper + row["THE_ID"] + "-" + row["THE_ORDER"];
}
- result: 3-ABC_18,4-ABC_56,5-DEF_03,6-XYZ_91,7-GHI_78
- At this time, a comma-separated list cannot be used for filter criteria
For More Information:
- iDashboards Builder Manual 14 Analytics
- iDashboards Data Hub Manual 8. Extract, Transform and Load
- OSKAR: Data Analytics
Disclaimer: iDashboards Technical Support Engineers are not Data Analysts who know in depth SQL Queries or JavaScript Expressions. We often learn these skills on the job and have limited knowledge. We do our best to help you with your software in determining if: you are not leveraging the software in the best way for your data or you found a bug in the software, because we want to assist you in your success. If you have issues with in depth queries or expressions please contact iDashboards Support and know that it will take a bit of time to figure out what is necessary for this issue, and if necessary we will refer you to your Client Success Manager to schedule time with a Principal Product Engineer.
If the above is unable to resolve the issue, then please contact iDashboards Support for further assistance.
Comments
0 comments
Please sign in to leave a comment.