I would really appreciate your help with an issue I have (I can't share the information due to security risks so I have to alter the case a bit).
I have a database in which there are three tables (ActionTimes, Order, Delivery) They look like this:
OrderId
ActionType
DateTime
1
1
2014-06-10 10:15:15.000
2
1
2014-06-10 12:31:52.000
1
2
2014-06-11 14:16:20.000
2
3
2014-06-15 13:51:06.000
OrderId
DeliveryId
Product
1
2
ProductA
2
1
ProductB
DeliveryId
DeliveryType
1
Pick up
2
Delivery
The problem i have is that I need to know the time per order. The times I get out of the first table. But with an order with DeliveryType "Delivery" I need to calculate the datetime of ActionType 2 - datetime of ActionType 1. For an order with DeliveryType "pick up" I need to calculate datetime of ActionType 3 - datetime of ActionType 1.
The SQL statement i have in Qlikview is the following:
Times_Temp:
LOAD OrderId,
DateTime as ActionTimes.DateTime1,
ActionType as ActionTimes.ActionType1;
SQL SELECT *
FROM "Database".ActionTimes
WHERE ActionType = '1';
Left Join (Times_Temp)
LOAD OrderId,
DateTime as ActionTimes.DateTime2,
ActionType as ActionTimes.ActionType2;
SQL SELECT *
FROM "Database".ActionTimes
WHERE TimeType = '2' and OrderId in (Select OrderId from "Database".Order where DeliveryId in (Select DeliveryId from "Database".Delivery where DeliveryType like '%Delivery%'));
Left Join (Times_Temp)
LOAD OrderId,
DateTime as ActionTimes.DateTime3,
ActionType as ActionTimes.ActionType3;
SQL SELECT *
FROM "Database".ActionTimes
WHERE TimeType = '3' and OrderId in (Select OrderId from "Database".Order where DeliveryId in (Select DeliveryId from "Database".Delivery where DeliveryType like '%Pick up%'));
ActionTimes:
Load
OrderId,
ActionTimes.DateTime1,
ActionTimes.ActionType1,
ActionTimes.DateTime2,
ActionTimes.ActionType2,
ActionTimes.DateTime3,
ActionTimes.ActionType3,
(time(ActionTimes.Datum3) - time(ActionTimes.Datum1))*1440 as ActionTimes.DurationMinutes
Resident Times_Temp;
Drop Table Times_Temp;
But in de SQL Statement on row 36 he should load the som of
