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: 
Margaret
Creator II
Creator II

AutoNumber(RowNo(),[MyField]) takes too long

I'm using AutoNumber(RowNo(),[MyField]) and it takes 25 minutes with 800k rows and I'm only going to have more as time goes by. (In SQL I'd use Row_Number() over Partition By, but I can't use that here.) How can I accomplish the same thing in substantially less time? 

Below is a Load Script that does what I want with some fake data. The point is to have a column that numbers the rows in which a patient visit took place in multiple departments on the same day. For example, a patient is admitted to the Emergency Department (ED) and transferred to the ICU. The ED row is '1', the ICU row is '2'. How can I do this for lots of data very quickly?

TempTable2:
LOAD * INLINE
[VisitNum, Dept, Date,
1000, ED, 2021-06-10
1001, ED, 2021-06-10
1001, ICU, 2021-06-10
1002, MedSurg, 2021-06-11
1003, ICU, 2021-06-10
1003, MedSurg, 2021-06-11
1004, ED, 2021-06-11
1004, MedSurg, 2021-06-11
];

TempTable1:
LOAD
*,
(VisitNum & '-' & Date) as PatientDayPresent
Resident TempTable2
;

DayPresent:
Load DISTINCT
*
,AutoNumber(RowNo(),PatientDayPresent) as PatientDayPresentIndex
Resident TempTable1;

Drop Tables TempTable2, TempTable1;

 

 

 

 

 

1 Solution

Accepted Solutions
rwunderlich

AutoNumber can be slow when you have lots of distinct AutoId values. A faster alternative can be to use Previous() and Peek() to process an ordered table. 

TempTable2:
LOAD *, RecNo() as RecId INLINE
[VisitNum, Dept, Date,

1000, ED, 2021-06-10
1001, ED, 2021-06-10
1001, ICU, 2021-06-10
1002, MedSurg, 2021-06-11
1003, ICU, 2021-06-10
1003, MedSurg, 2021-06-11
1004, ED, 2021-06-11
1004, MedSurg, 2021-06-11
];

DayPresent:
LOAD
*,
if (Previous(VisitNum) = VisitNum and Previous(Date) = Date, peek('PatientDayPresentIndex') + 1, 1) as PatientDayPresentIndex
Resident TempTable2
Order by VisitNum, Date, RecId
;
Drop Table TempTable2; 

Note I assigned a "recId" in the initial load as a way of maintaining the order of the intra-day events. 

-Rob

View solution in original post

2 Replies
rwunderlich

AutoNumber can be slow when you have lots of distinct AutoId values. A faster alternative can be to use Previous() and Peek() to process an ordered table. 

TempTable2:
LOAD *, RecNo() as RecId INLINE
[VisitNum, Dept, Date,

1000, ED, 2021-06-10
1001, ED, 2021-06-10
1001, ICU, 2021-06-10
1002, MedSurg, 2021-06-11
1003, ICU, 2021-06-10
1003, MedSurg, 2021-06-11
1004, ED, 2021-06-11
1004, MedSurg, 2021-06-11
];

DayPresent:
LOAD
*,
if (Previous(VisitNum) = VisitNum and Previous(Date) = Date, peek('PatientDayPresentIndex') + 1, 1) as PatientDayPresentIndex
Resident TempTable2
Order by VisitNum, Date, RecId
;
Drop Table TempTable2; 

Note I assigned a "recId" in the initial load as a way of maintaining the order of the intra-day events. 

-Rob

View solution in original post

Margaret
Creator II
Creator II
Author

Thank you, that worked!