Finding (returning) value in child records

[pdufs40x]

I got a table named "Order", an order has multiple child records in the table "OrderStatus". The orderstatus contains steps following the progress of an order. The orderstatus contains a datetime field holding a timestamp when the specific status was finialized. I need to get the datestamp of a specific type of status (say 160) -IF- it exists for the order, otherwise fallback to the date of status 110 (which should always be present).

A simplified example of the database has been set up here:

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=078d09c494bdfd18374bdf81d61d19a5

Thanks to stackoverflow i did achieve it in sql using the following query:

Spoiler

SELECT o.orderid,
       COALESCE(loadedTime.date, pendingTime.date) AS time_stamp
FROM   [order] o
       JOIN orderstatus pendingTime
         ON pendingTime.orderid = o.orderid
            AND pendingTime.type = 110
       LEFT JOIN orderstatus loadedTime
              ON loadedTime.orderid = o.orderid
                 AND loadedTime.type = 160 

Now the challenge in Qlik is that i first have to build up my order table. After the order table we build up the orderstatus table from a different data source. After this i should somehow query the orderstatus and extend the order table with a new field; completeDate field with the result returned from the above query.

[treysmithdev]

I believe using ApplyMap would be the most efficient way to do this:

OrderStatus_110: // Default: NULL
Mapping Load
    OrderId,
    StatusTimestamp
From
    [....]
Where
    OrderStatus = '110';


OrderStatus_160: // Default: ApplyMap('OrderStatus_110',OrderId,Null())
Mapping Load
    OrderId,
    StatusTimestamp
From
    [....]
Where
    OrderStatus = '160';




Orders:
Load
    OrderId,
    OrderDesc,
    Qty,
    Amt,
    ApplyMap('OrderStatus_160',OrderId,ApplyMap('OrderStatus_110',OrderId,Null())) as TimeStamp
From
    [....];