Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Direct Discovery - Union Query

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






Community Browser