Forum Discussion
Helper IIGet Info from Previous Row (Again)
It seems I cannot get my head around this.
I looked at most posts on the same subject but I cannot create the proper column.
Here's a sample data (table name = invtrx)
warehouse item date qty flag
AAA 12345 12/12/2022 1 Y
AAA 12345 14/12/2022 1 N
AAA 12345 16/12/2022 -1 (I want this one to be the last one ) N
How can I create a column that would pick up only the last flag before the transaction on the 16/12/2022 ?
I might add additional filters. I think that because there might be additional transaction on the same date I should create an index in M.
THanks
Francois
francoisl Assuming you want a calculated column and assuming you have an index, you could do this as a new calculated column:
Calculated Column = VAR __Flag = [flag] VAR __Warehouse = [warehouse] VAR __Item = [item] VAR __Date = [date] VAR __Index = [index] VAR __Result = IF( __Flag <> BLANK(), __Flag, VAR __Previous = MAXX( FILTER( 'Table', [warehouse] = __Warehouse && [item] = __Item && [date] < __Date && [flag] <> BLANK() ), [index]) VAR __Result = MAXX( FILTER( 'Table', [index] = __Previous), [flag] ) RETURN __Result ) RETURN __Result
2 Replies
- Greg_Deckler
Community Champion
francoisl Assuming you want a calculated column and assuming you have an index, you could do this as a new calculated column:
Calculated Column = VAR __Flag = [flag] VAR __Warehouse = [warehouse] VAR __Item = [item] VAR __Date = [date] VAR __Index = [index] VAR __Result = IF( __Flag <> BLANK(), __Flag, VAR __Previous = MAXX( FILTER( 'Table', [warehouse] = __Warehouse && [item] = __Item && [date] < __Date && [flag] <> BLANK() ), [index]) VAR __Result = MAXX( FILTER( 'Table', [index] = __Previous), [flag] ) RETURN __Result ) RETURN __Result
francoisl