Conditional return of datafield

pdufs40x — Fri, 06 Sep 2019 14:37:43 GMT

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 simple example of the datebase 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:

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

Re: Conditional return of datafield

Brett_Bleess — Thu, 19 Sep 2019 14:34:41 GMT

Laurent, likely going to need to attach a sample QVW file on this one in order for someone to offer up suggestions, as I am not sure the SQL query is going to be enough for them to sort it in QlikView.

Regards,
Brett

topic Conditional return of datafield in QlikView

Conditional return of datafield

Re: Conditional return of datafield