Qlik Community

Ask a Question

App Development

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

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
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.