0 Replies Latest reply: May 4, 2017 5:28 AM by Chi Yeung Wang RSS

    Convert complex SQL to qlik expression

    Chi Yeung Wang

      Dear all,

       

      I have a table "Leased"

      TranDateLeaseIDDoCAreaStatus
      2017/04/01L00012016/01/0110001
      2017/05/01L00012016/01/0110001
      2018/01/01L00012018/01/1510003
      2018/02/01L00012018/01/1510003
      2018/03/01L00012018/01/1510003
      2017/04/01L00022016/01/0120001

       

      I have a query to display all the Lease with Status = 1 and TranDate = '2017/04/01'

      Sum({$<TranDate = {'2017/04/01'}, Status = {1}>} Area)

      which eq to SQL:

      select sum(Area) from table where TranDate = '20170401' and Status = 1

       

      And I want to have 2 formulas which something like this SQL

       

      -- Select area which have other status

      select Status, sum(Area) from

      (

      select distinct LeaseID, DoC, Area, Status from table

      where TranDate >= DoC

      and Status <> 1

      and LeaseID in (select LeaseID from table

                              where TranDate = '20170401' and Status = 1)

      ) t

      -- Should be 1000

       

      I try this expression

      Sum(

      Aggr(

      Sum({1<

        Status -= {1},

              LeaseID = P({1<TranDate = {'2017/04/01'}, Status = {1}>} LeaseID),

              TranDate = {'>=$(AddMonths(DoC, 0))'}

            >}

        Distinct LeasedIFA), LeaseID, DoC, LeasedIFA))


      The total "LeasedIFA" is correct, but not categorize in correct status

       

      -- Select area which do not have other status

      select sum(Area) from table

      where TranDate = '20170401'

      and Status = 1

      and LeaseID not in (select LeaseID from table

                                    where TranDate >= '20170401' and Status <> 1)

      -- Should be 2000

       

      And at the end, I want to display a bar chart with group the 2nd SQL into Status 4

       

      Could it happen in Qlik Expression? Or I should do that in SQL first, and import into Qlik?

       

      Thanks and best wishes,

      Alex