Create a new column result from multiple rows

[timealan]

Hello,

I have a realtively simple data set. Purchase order / Purchase Order Line / OT.

"OT" is a previously added column that checks whether or not the receipts against the line are "ONTIME" or "LATE"...

I would like to create a two new columns:

"LINE-OT" that tells me whether the PO LINE as a whole is either "ONTIME" (ie all "OT" against that line "ONTIME") or "LATE" (ie at least one "OT" against the PO line is "Late")

"PO-OT" that tells me whether all the POLINE on that PO are either "ONTIME" (ie all "LINEOT" are "ONTIME") or "LATE" (ie at least on "LINEOT" relating to the PO is "LATE")

Whats the best approach to this? I can add reasonably complicated condtional columns, but am unsure how to base the condition on a subset of data.

Thanks

Simon

[timealan]

Thank you very much. I tip my hat to you. 

[amitchandak · Accepted solution]

@timealan , Both as new columns

PO Status =
var _late = countx(filter(table, [purchase_order] =earlier([purchase_order]) && [OT] = "LATE"),[purchase_order])+0
return
if(_late >0 , "LATE" , "ONTIME")

PO_LINE Status =
var _late = countx(filter(table, [purchase_order] =earlier([purchase_order]) && [PO_LINE] =earlier([PO_LINE]) && [OT] = "LATE"),[purchase_order])+0
return
if(_late >0 , "LATE" , "ONTIME")