Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sequence Help

Hello,

I am looking for a way to create a custom sequence number based on existing data in my dashboard. The data that I have loaded is based on sequencing from two different sources. The 'M#' is the original planned sequence value; and the 'D#' is the adjusted plan sequence, with the 'Actual Time' value being the time stamp of the actual activity:

Example

RID     LOC     M#     D#     Actual Time

ABC123     123     3     12     5:44:09 AM

ABC123      456     2     5     7:37:25 AM

ABC123     789     11     4     7:46:36 AM


Part of the problem is that the 'Actual Time' expression is using the Min() function and then I am using a sort on this field. I want to replace the time stamp with a sequence number (1, 2, 3, etc.) based on this sort. However, any of the options that I have already explored do not appear to preserve the sort that i already have.


Any assistance in this matter would be appreciated.


Thanks!

5 Replies
sunny_talwar

Have you tried just RowNo() or RowNo(TOTAL)?

Anil_Babu_Samineni

Do you mean, you want to show serial numbers depends on time stamp which is from asc to desc??

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

Use rowan() function

Table A:

Load

*

Resident

<Table>

order by Actual Time

Table B:

Noconcatenate

Load

*,

rowno()

Resident

Table A;

you can do this in single table load as well.

Not applicable
Author

Yes, i tried RowNo(TOTAL) and this does give me a sequence. However, it does not sort in the desired order. I found the following thread which does explain this sorting issue:

interactive sort with RowNo(Total) does not work

Not applicable
Author

I came close to what I need using the following expression:

=aggr(mod(RowNo(TOTAL)-1,Count(DISTINCT TOTAL [LocationKey]))+1,[LocationKey])

However, I need it to work with the "Actual Arrive" time, which as mentioned is an expression of Min(StartTime). Attached is a screen shot of what I am looking for.

thanks!

Example_2.png