Convert complex SQL to qlik expression

alexlinkreit17 — Thu, 04 May 2017 08:37:14 GMT

Dear all,

I have a table "Leased"

TranDate	LeaseID	DoC	Area	Status
2017/04/01	L0001	2016/01/01	1000	1
2017/05/01	L0001	2016/01/01	1000	1
2018/01/01	L0001	2018/01/15	1000	3
2018/02/01	L0001	2018/01/15	1000	3
2018/03/01	L0001	2018/01/15	1000	3
2017/04/01	L0002	2016/01/01	2000	1

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

topic Convert complex SQL to qlik expression in QlikView