Fill a column all records, with values from a 2nd column based on 3rd column having unique IDs

[rashidanwar]

Hi everybody,

I have an order table with multipe orderlines against a unique order ID. 
Requirement: If status column contains shipped or ready even for a one line item I want to fill all the rows for that specific order ID in custom_column  with the word shipped.
order_id             line_item         status            custom_column
1                        abc                  shipped         shipped
1                        def                   pending        shipped
1                        ghi                   ready             shipped
1                        klm                  shipped          shipped
2                        nop                 penidng 
2                        qrs                  penidng

Thank you so much for any help in this regard.
Rashid Anwar

[amitchandak · Accepted solution]

@rashidanwar , a new column

new column =
var _cnt = countx(filter(Table, [order_id] =earlier([order_id]) && [status] in {"shipped","ready"}),[Order_id])
return
if(isblank(_cnt), blank(), "shipped")

[rashidanwar]

@amitchandak Thank you so much man.
It worked!
Can you please shed some light on that how actually countx is working in this specific context?
Thank you.