Hi all,
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
time(ActionTimes.Datum3) - time(ActionTimes.Datum1)
when DeliveryType = "Pick up" and he should load the som of
time(ActionTimes.Datum2) - time(ActionTimes.Datum1)
when DeliveryType = "Delivery"
If it helps, I got 5 different delivery types
I would really appreciate your help!
