https://community.qlik.com/t5/Suggest-an-Idea/Equivalent-of-SQL-OVER-PARTITION-BY-ORDER-BY-Functionality/idi-p/1759151 <P>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.</P><P>Suppose you have a list of events and categories, and you want to number the events in order of date within each category:</P><TABLE border="1"><TBODY><TR><TD>event type</TD><TD>date</TD></TR><TR><TD>Picnic</TD><TD>2019-03-04</TD></TR><TR><TD>Festival</TD><TD>2019-04-05</TD></TR><TR><TD>Picnic</TD><TD>2019-05-06</TD></TR><TR><TD>Festival</TD><TD>2019-06-07</TD></TR></TBODY></TABLE><P>In SQL you would write:</P><P>&nbsp;</P><LI-CODE lang="sql">SELECT [event type], [date], ROW_NUMBER() OVER (PARTITION BY [event type] ORDER BY [date] ASC) as [event number]</LI-CODE><P>&nbsp;</P><P>This would produce:</P><TABLE border="1"><TBODY><TR><TD>event type</TD><TD>date</TD><TD>event number</TD></TR><TR><TD>Picnic</TD><TD>2019-03-04</TD><TD>1</TD></TR><TR><TD>Picnic</TD><TD>2019-05-06</TD><TD>2</TD></TR><TR><TD>Festival</TD><TD>2019-04-05</TD><TD>1</TD></TR><TR><TD>Festival</TD><TD>2019-06-07</TD><TD>2</TD></TR></TBODY></TABLE><P>The equivalent process in Qlik script is much trickier. e.g. <A href="https://community.qlik.com/t5/QlikView-Scripting/ROW-NUMBER-OVER-PARTITION-BY-A-B-C-ordered-by-x/td-p/123009" target="_self">See this forum question.</A></P> Wed, 25 Nov 2020 08:50:26 GMT daviewales 2020-11-25T08:50:26Z https://community.qlik.com/t5/Suggest-an-Idea/Equivalent-of-SQL-OVER-PARTITION-BY-ORDER-BY-Functionality/idc-p/1784709#M5190 <P>Thank you for your feedback on ways to improve our product. While this is something we understand would be useful, it's not on the short-term roadmap. Please continue to show your support for this idea.</P><P>Thanks,</P><P>Patric</P> Mon, 22 Feb 2021 13:37:54 GMT https://community.qlik.com/t5/Suggest-an-Idea/Equivalent-of-SQL-OVER-PARTITION-BY-ORDER-BY-Functionality/idc-p/1784709#M5190 Patric_Nordstrom 2021-02-22T13:37:54Z https://community.qlik.com/t5/Suggest-an-Idea/Equivalent-of-SQL-OVER-PARTITION-BY-ORDER-BY-Functionality/idc-p/2113382#M15115 <P>From now on, please track this idea from the Ideation portal.&nbsp;</P><P><STRONG><A title="Link to new idea" href="https://ideation.qlik.com/app/#/case/274749" target="_blank" rel="noopener">Link to new idea</A></STRONG></P><P>Meghann</P><P data-unlink="true"><EM>NOTE: Upon clicking this link 2 tabs may open - please feel free to close the one with a login page. If you <STRONG>only</STRONG> see 1 tab with the login page, please try clicking this link first: <STRONG><A title="Authenticate me!" href="#" target="_blank" rel="noopener">Authenticate me!</A></STRONG>&nbsp;t</EM><EM>hen try the link above again. Ensure pop-up blocker is off.</EM></P> Tue, 05 Sep 2023 13:02:00 GMT https://community.qlik.com/t5/Suggest-an-Idea/Equivalent-of-SQL-OVER-PARTITION-BY-ORDER-BY-Functionality/idc-p/2113382#M15115 Meghann_MacDonald 2023-09-05T13:02:00Z https://community.qlik.com/t5/Suggest-an-Idea/Equivalent-of-SQL-OVER-PARTITION-BY-ORDER-BY-Functionality/idc-p/2113383#M15116 Tue, 05 Sep 2023 13:02:02 GMT https://community.qlik.com/t5/Suggest-an-Idea/Equivalent-of-SQL-OVER-PARTITION-BY-ORDER-BY-Functionality/idc-p/2113383#M15116 Ideation 2023-09-05T13:02:02Z