topic Re: Sequence Help in QlikView

Sequence Help

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Sequence Help

sunny_talwar — Wed, 12 Oct 2016 20:27:36 GMT

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

Re: Sequence Help

Anil_Babu_Samineni — Thu, 13 Oct 2016 01:22:10 GMT

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

Re: Sequence Help

Anonymous — Thu, 13 Oct 2016 02:55:18 GMT

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.

Re: Sequence Help

Fri, 14 Oct 2016 17:17:15 GMT

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

Re: Sequence Help

Fri, 14 Oct 2016 21:12:40 GMT

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!