Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
default_the_pig
Contributor
Contributor

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

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!

1 Solution

Accepted Solutions
Nicole-Smith

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;

 

 

View solution in original post

2 Replies
Nicole-Smith

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;

 

 

View solution in original post

default_the_pig
Contributor
Contributor
Author

Thank you, Nicole! That did the trick.