Hi all,
Here is an example of a simple SQL query:
SELECT
'Order Type 1' as OrderType,
o.Order_ID,
m.memberID,
o.Price
FROM MyDatabase.dbo.Orders o
JOIN MyDatabase.dbo.Memebrs m on m.id=o.member_id
Where o.status='paid'
UNION ALL
SELECT
'Order Type 2' as OrderType,
o.Order_ID,
m.memberID,
o.Price
FROM MyDatabase.dbo.Orders o
JOIN MyDatabase.dbo.Memebrs m on m.id=o.member_id
Where o.status='cancelled' and
(case when o.typeDate=1 then o.Date else o.DeliveryDate end) < 2015-01-01
The question is:
How can I integrate this kind of query (UNION, CASE When, Alias, ...) on a Direct query in Qlikview.
I know how to do this for the first part of my query:
DIRECT QUERY
DIMENSION
'Order Type 1' as OrderType,
MyDatabase.dbo.Orders.Order_ID as OrderID,
MyDatabase.dbo.Members.memberID as MemberID
MEASURE
MyDatabase.dbo.Orders.Price as Price
FROM MyDatabase.dbo.Orders
JOIN MyDatabase.dbo.Members m on m.id=o.member_id
Where MyDatabase.dbo.Orders.status='paid'
But How can I add the Union part and the second part of the query with the case when statement?
Thank you very much for your help.
Jeremy