Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;
Thank you, Nicole! That did the trick.