Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Have you tried just RowNo() or RowNo(TOTAL)?
Do you mean, you want to show serial numbers depends on time stamp which is from asc to desc??
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.
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:
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!