Direct Discovery - Union Query

Mon, 08 Feb 2016 16:58:09 GMT

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

topic Direct Discovery - Union Query in QlikView