Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How to write window function (dense rank) in QlikView
Ex:
SELECT *
FROM
(SELECT *, last_value(cte1.DensRank) OVER (PARTITION BY OfficeName,OfficeGroup,[Month],[Week] ORDER BY OfficeType ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) as MaxValue
FROM
(SELECT *,
dense_rank() OVER (PARTITION BY OfficeName,OfficeGroup,[Month],[Week] ORDER BY OfficeType) as DensRank
FROM tblOffice) cte1) cte2
WHERE
(cte2.MaxValue >= 3 AND OfficeGroup= N'Fruit')
OR
(cte2.MaxValue >= 2 AND OfficeGroup = N'Rice')
ORDER BY OfficeName,OfficeGroup,[Month],[Week], OfficeType
Check https://community.qlik.com/t5/QlikView-Documents/Continuous-Ranking-no-missing-Rank/ta-p/1486407