Info |
---|
NOTE: This function has been superseded by the $sourcerownumber reference. While this function is still usable in the product, it is likely to be deprecated in a future release. Please use $sourcerownumber instead. For more information, see Source Metadata References. |
Excerpt |
---|
Returns the row number of the current row as it appeared in the original source dataset before any steps had been applied. |
The following transforms might make original row information invalid or otherwise unavailable. In these cases, the function returns null values:
pivot
flatten
join
lookup
union
unnest
unpivot
Info |
---|
NOTE: This function does not apply to relational database sources. |
Info |
---|
NOTE: If the dataset is sourced from multiple files, a predictable original source row number cannot be guaranteed, and null values are returned. |
Tip |
---|
Tip: If the source row information is still available, you can hover over the left side of a row in the data grid to see the source row number in the original source data. |
Example:
D lang syntax |
---|
sourcerownumber() |
Output: Returns the source row number for each row as it appeared in the original data.
Sort Example:
D trans |
---|
RawWrangle | true |
---|
Type | step |
---|
WrangleText | sort order: sourcerownumber() |
---|
p01Name | Sort by |
---|
p01Value | sourcerownumber() |
---|
SearchTerm | Sort rows |
---|
|
Output: Rows in the dataset are re-sorted according to the original order in the dataset.
Delete Example:
D trans |
---|
RawWrangle | true |
---|
p03Value | sourcerownumber() > 101 |
---|
Type | step |
---|
WrangleText | delete row:sourcerownumber() > 101 |
---|
p01Name | Condition |
---|
p01Value | Custom formula |
---|
p02Name | Type of formula |
---|
p02Value | Custom single |
---|
p03Name | Condition |
---|
p04Value | Delete matching rows |
---|
p04Name | Action |
---|
SearchTerm | Filter rows |
---|
|
Output: Deletes the rows in the dataset that were after row #101 in the original source data.
There are no arguments for this function.
Include Page |
---|
| EXAMPLE - SOURCEROWNUMBER Function |
---|
| EXAMPLE - SOURCEROWNUMBER Function |
---|
|
Example - Using sourcerownumber to create unique row identifiers
The following example demonstrates how to unpack nested data. As part of this example, the SOURCEROWNUMBER
function is used as part of a method to create unique row identifiers.
Include Page |
---|
| EXAMPLE - Flatten and Unnest Transforms |
---|
| EXAMPLE - Flatten and Unnest Transforms |
---|
|
Example - Delete rows based on source row numbers
Source:
Your dataset is the following set of orders.
CustId | FirstName | LastName | City | State | LastOrder |
---|
1001 | Skip | Jones | San Francisco | CA | 25 |
1002 | Adam | Allen | Oakland | CA | 1099 |
1003 | David | Wiggins | Oakland | MI | 125.25 |
1004 | Amanda | Green | Detroit | MI | 452.5 |
1005 | Colonel | Mustard | Los Angeles | CA | 950 |
1006 | Pauline | Hall | Sagninaw | MI | 432.22 |
1007 | Sarah | Miller | Cheyenne | WY | 724.22 |
1008 | Teddy | Smith | Juneau | AK | 852.11 |
1009 | Joelle | Higgins | Sacramento | CA | 100 |
Transformation:
Initially, you want to review your list of orders by last name.
D trans |
---|
RawWrangle | true |
---|
Type | step |
---|
WrangleText | sort order:LastName |
---|
p01Name | Sort by |
---|
p01Value | LastName |
---|
SearchTerm | Sort rows |
---|
|
During your review, you notice that two customer orders are no longer valid and need to be removed. They are:
- LastName: Hall
- LastName: Jones
You might hover over the left side of the screen to reveal the row numbers. You select the row numbers for each of these rows, and a delete suggestion is provided for you. When you click Modify, you see the following transformation:
D trans |
---|
RawWrangle | true |
---|
p03Value | in(sourcerownumber(), [2,7]) |
---|
Type | step |
---|
WrangleText | delete row: in(sourcerownumber(), [2,7]) |
---|
p01Name | Condition |
---|
p01Value | Custom formula |
---|
p02Name | Type of formula |
---|
p02Value | Custom single |
---|
p03Name | Condition |
---|
p04Value | Delete matching rows |
---|
p04Name | Action |
---|
SearchTerm | Filter rows |
---|
|
The above checks the results of the sourcerownumber
function, which returns the original row order for the selected rows. If a selected row matches values in the [2,7]
array of row numbers, then the row is deleted.
Results:
When the preceding transform is added, your dataset looks like the following, and your sort order is maintained:
Source:
CustId | FirstName | LastName | City | State | LastOrder |
---|
1002 | Adam | Allen | Oakland | CA | 1099 |
1004 | Amanda | Green | Detroit | MI | 452.5 |
1009 | Joelle | Higgins | Sacramento | CA | 100 |
1007 | Sarah | Miller | Cheyenne | WY | 724.22 |
1005 | Colonel | Mustard | Los Angeles | CA | 950 |
1008 | Teddy | Smith | Juneau | AK | 852.11 |
1003 | David | Wiggins | Oakland | MI | 125.25 |