...
Code Block |
---|
samples/transformations/Analytic Query - Lead One Example.ktr samples/transformations/Analytic Query - Random Value Example.ktr |
Group field examples
While it is not mandatory to specify a group, it can be useful for certain cases. If you create a group (made up of one or more fields), then the "lead forward / lag backward" operations are made only within each group. For example, suppose you have this:
Code Block |
---|
X , Y
--------
aaa , 1
aaa , 2
aaa , 3
bbb , 4
bbb , 5
bbb , 6
|
And you want to create a field named Z, with the Y value in the previous row.
If you only care about the Y field, you don't need to group. And you will have the following result:
Code Block |
---|
X , Y , Z
------------
aaa , 1 , <null>
aaa , 2 , 1
aaa , 3 , 2
bbb , 4 , 3
bbb , 5 , 4
bbb , 6 , 5
|
But if you don't want to mix the values for aaa and bbb, you can group by the X field, and you will have this:
Code Block |
---|
X , Y , Z
------------
aaa , 1 , <null>
aaa , 2 , 1
aaa , 3 , 2
bbb , 4 , <null>
bbb , 5 , 4
bbb , 6 , 5
|
Thus, by grouping (provided the input is sorted according to your grouping), you can be assured that lead or lag operations will not return row values outside of the defined group.
External Links
- Blog on the new Analytic Query step (w/ some screenshots) : The Death of prevrow = row.clone()