It would be really helpful to include support for analytic functions in the Qlik Sense load script. This allows you to partition data into groups, and apply functions on those groups.
Suppose you have a list of events and categories, and you want to number the events in order of date within each category:
| event type | date |
| Picnic | 2019-03-04 |
| Festival | 2019-04-05 |
| Picnic | 2019-05-06 |
| Festival | 2019-06-07 |
In SQL you would write:
SELECT
[event type],
[date],
ROW_NUMBER() OVER (PARTITION BY [event type] ORDER BY [date] ASC) as [event number]
This would produce:
| event type | date | event number |
| Picnic | 2019-03-04 | 1 |
| Picnic | 2019-05-06 | 2 |
| Festival | 2019-04-05 | 1 |
| Festival | 2019-06-07 | 2 |
The equivalent process in Qlik script is much trickier. e.g. See this forum question.
