Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

alexlinkreit17
New Contributor

Convert complex SQL to qlik expression

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

Tags (2)
Community Browser