D toc |
---|
Info |
---|
NOTE: This function has been superseded by the |
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 inputs, such as Hive or JDBC 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. |
D s | ||
---|---|---|
|
Derive Example:
D code |
---|
derive type:single value:SOURCEROWNUMBER() as:'OriginalRowNums' |
Output: Generates a new OriginalRowNums
column containing the row numbers for each row as it appeared in the original data.
Sort Example:
D code |
---|
sort order: SOURCEROWNUMBER() |
Output: Rows in the dataset are re-sorted according to the original order in the dataset.
Delete Example:D code |
---|
delete row:SOURCEROWNUMBER() > 101 |
Output: Deletes the rows in the dataset that were after row #101 in the original source data.
D s | ||
---|---|---|
|
There are no arguments for this function.
D s | ||
---|---|---|
|
Example - Header from row that is not the first one
Include Page | ||||
---|---|---|---|---|
|
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 - 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 |
Transform:
Initially, you want to review your list of orders by last name.
D code |
---|
sort order:LastName |
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 transform:
D code |
---|
delete row: IN(SOURCEROWNUMBER(), [2,7]) |
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 |
D s also | ||
---|---|---|
|