Product: TIBCO Spotfire®
How to Get a Calculated Column with Similar Results to Function LastValidBefore But in a Certain Order for the Rows
If you need to fill in missing values in a column with the last valid value from the rows above each missing value, you can use custom expression function LastValidBefore(). But this function uses the default row order from when the table is imported into Spotfire. If you need to do the calculation based on a certain custom order (based on a column sorting), this function does not give the desired results.
For example, you have a data table as the following:
A B 1 0.3 2 3 0.6 4 0.1 5 6 7 0.5 8 0.5
And you want to substitute the NULL values in column B with the last non-NULL value, based on the index in column A, to get a calculated column C, resulting in the desired outcome of:
A B C 1 0.3 0.3 2 0.3 3 0.6 0.6 4 0.1 0.1 5 0.1 6 0.1 7 0.5 0.5 8 0.5 0.5This can easily be accomplished with the expression:
?LastValidBefore([B])But if your data is not in the desired order (matching the order of column A which you are looking to use in your ordering), like:
A B 1 0.3 2 5 6 7 0.5 8 0.5 3 0.6 4 0.1Then the LastValidBefore([B]) expression will not work since that function is dependent on the default row order from when the table is imported into Spotfire. Using that same expression would now result in:
A B C 1 0.3 0.3 2 0.3 5 0.3 6 0.3 7 0.5 0.5 8 0.5 0.5 3 0.6 0.6 4 0.1 0.1which is not the desired outcome as seen above.
To perform a similar calculation to LastValidBefore(), but based on a custom ordered index column (column A in our case) and not the default row order of the imported data table, then the expression you need to use should be:
LastValueForMax([A],[B]) OVER (AllPrevious([A]))Resulting in:
A B C 1 0.3 0.3 2 0.3 5 0.1 6 0.1 7 0.5 0.5 8 0.5 0.5 3 0.6 0.6 4 0.1 0.1Or when ordered per column A:
A B C 1 0.3 0.3 2 0.3 3 0.6 0.6 4 0.1 0.1 5 0.1 6 0.1 7 0.5 0.5 8 0.5 0.5
Doc: Statistical Functions
Comments
0 comments
Article is closed for comments.