load with if statement over three tables

Fri, 20 Nov 2020 18:02:30 GMT

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!

Re: load with if statement over three tables

Gysbert_Wassenaar — Fri, 20 Jun 2014 13:35:22 GMT

See attached qvw.

topic Re: load with if statement over three tables in QlikView

load with if statement over three tables

Re: load with if statement over three tables