topic How to convert DENSE_RANK Over Partition BY into Qlik Sense in Load Script in App Development

How to convert DENSE_RANK Over Partition BY into Qlik Sense in Load Script

default_the_pig — Fri, 14 May 2021 16:24:37 GMT

I'm trying to convert the following from SQL to Qlik Sense:

CONCAT('Invoice ', DENSE_RANK() OVER (PARTITION BY [Account Number], [Type ] ORDER BY [Year Month])),

I'm trying to do "Invoice 1", Invoice 2", "Invoice 3" etc etc. This was a calculated field before in SQL but I need to know how to convert this into Qlik and I'm not sure the best way how exactly. I've seen articles where it's been pulled off on the front end but I want to see if there is a way to pull this off in the load script.

Any help is greatly appreciated!

Re: How to convert DENSE_RANK Over Partition BY into Qlik Sense in Load Script

Nicole-Smith — Mon, 17 May 2021 14:24:25 GMT

I believe load script like this should do the trick:

Data: LOAD * INLINE [ Account Number, Type, Year Month 1, A, 202101 1, A, 202102 1, A, 202103 2, A, 202102 2, A, 202103 2, A, 202104 3, B, 202103 3, B, 202104 3, B, 202105 ]; Final: LOAD [Account Number], [Type], [Year Month], IF([Account Number] = PREVIOUS([Account Number]) AND [Type] = PREVIOUS([Type]), PEEK([Invoice Number])+1, 1) AS [Invoice Number] RESIDENT Data ORDER BY [Type], [Account Number], [Year Month]; DROP TABLE Data;

Re: How to convert DENSE_RANK Over Partition BY into Qlik Sense in Load Script

default_the_pig — Mon, 17 May 2021 15:57:16 GMT

Thank you, Nicole! That did the trick.