0 Replies Latest reply: Feb 8, 2016 11:58 AM by Jeremy Soultanny RSS

    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