I have one table ,which has field A-C. field A +field B will be an unique Key, and the data will be sorted by field A and fieldB. now I want to get the min Field C, by each record before the current record.

[WuCong]

as you can see ,1-8 are subject001 ,for No2 ,I will get 1's min information ,for No4,I want to get No1-No3's min FieldC.

for each record ,I want to get the min FieldC before current record group by Field A.

the result I expected like :

[APrasad_Tri]

Hi @wu cong?, thanks for reaching out.

To get the minimum of current and previous rows based on grouping conditions, you must create a column that displays the last row value. To get this, we use the Window function - PREV.

PREV({Field C}, 1)

In this function mention the field name and number of rows. I have selected 1 because we are capturing the value of one row above.

Here, sorting is done by Field B to get the correct alignment of the rows.

Next, we use the window function - ROLLINGMIN to get the minimum amongst the rows

Here, sorting is done by Field B to get the correct alignment of the rows.

Select the missing values from the data quality bar and using the transformative suggestions, assign 0 to the null values

Hope this helps. Please do let me know if you have any additional queries.

Best,

Apeksha