Your requirement could be accomplished with source qualifier and expression transformations.
1. Source definition
2. In source qualifier transformation, sort the data on TranID
3. In expression transformation,
Instance=IIF(ISNULL(Channel) AND TranID=TranID_Temp, Channel_Temp, Channel)
4. Populate the TranID, Instance, ProdID, UnitPrice, Qty, LineItemValue to target
A similar question has been asked recently so you could check recent discussions as well but I will repeat the answer here.
Basically there are 2 options
Option 1 is to read the rows sorted on TranID and instance (desc) and you must be sure the first record for a TranID has the instance information.
Next Transformation can be an EXP in which you add as the very last port a variable port var_prev_TranID (it's essential it's the last port in the expression) and the expression information will be port TranID
The Instance port must be named in_Instance followed by a variable port var_Instance and the expression for this on must be
IIF(TranID <> var_prev_TranID, in_Instance, var_Instance)
Next port can be out_Instance with the expression var_Instance
Check also post MAX values
Now if you want to use PDO you can use the following logic (works also in normal mapping but much more resources consuming)
You need to read the source twice, once with all the fields and once with only the TranID and intance port.After reading the TranID and Instance you aggregate (TranID = group by port) and you need to pick the max value for Instance given it isn't a Null value.
Now you can join on TranID and keep the aggregated instance value for the records.
Check also post on Get MAX values
Hope this helps
Is your issue solved or do you require additional assistance?
Thank you JanLeendert!
It worked as expected.
Thank you Lekha GM!