Generate unique number based on condition

[rkpatelqlikview]

Dear Team,

Please help on this scenario. how to generate the numbers. Below example

Each HeaderID is having seq.numbers and status. I want to see the number like below in result. 

HeaderID '1' is having seqnum. 5,7,8 for Approve so my resultant should be 1,2,3. For cancel also same. There is only one cancel '8' so Result is 1, if another number exists then Result should be '2'.

Thanks in advance. 

[Edvin]

Hello,

Try creating a measure with an expression:

=aggr(NumberNo(),Header_ID,Status,SEQ_NUM) or try without SEQ_NUM if that doesn't work.

Hope this helps you.

[rkpatelqlikview]

Thanks for your reply Edwin, It should be done in backend. I tried with Autonumber()  and Recno() as  well.

I think these two functions are suitably to implement this. 

[Edvin · Accepted solution]

Ahh, alright.

LOAD Header_ID,
SEQ_NUM,
Status,
AutoNumber(RowNo(),Header_ID&Status) as Result
FROM [...]

Try this again.

[rkpatelqlikview]

Hello Edwin, This is not working.  Its taking more timi for reload, even am not sure for the result.  May be we need to use Iter No some thing. 

[Edvin]

Not sure why it's taking longer, but for me it works perfectly. Maybe did a mistake by inserting new line?

This code works for me:
LOAD *,
AutoNumber(RowNo(), Header_ID&Status) as Result;

LOAD * INLINE [
Header_ID, SEQ_NUM, Status
1, 5, Approved
1, 7, Approved
1, 11, Approved
1, 8, Cancel
21, 2, Approved
21, 4, Cancel
21, 4, Approved
22, 2, Approved
22, 4, Approved
81, 2, Approved
81, 4, Approved
];

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD * INLINE [
    Header_ID, SEQ_NUM, Status
    1, 5, Approve
    1, 7, Approve
    1, 11, Approve
    1, 8, Cancel
    21, 2, Approve
    21, 4, Cancel
    21, 4, Approve
    22, 2, Approve
    22, 4, Approve
    81, 2, Approve
    81, 4, Approve
];

FinalTable:
LOAD *,
	 If(Header_ID = Previous(Header_ID) and Status = Previous(Status), RangeSum(Peek('Result'), 1), 1) as Result
Resident Table
Order By Header_ID, Status;

DROP Table Table;

[rkpatelqlikview]

Many thanks . Yes Correct approach Edwin.  I implemented same in my original script which is more data.

When the data  size is  big then it is taking time. But we need to think another way for large set of data. 

[rkpatelqlikview]

Thanks Sunny, Finally succeed. Just i put SEQ_NUM asc because  i need result  based on seq_num also.

FinalTable:

LOAD *,

If(Header_ID = Previous(Header_ID) and Status = Previous(Status), RangeSum(Peek('Result'), 1), 1) as Result

Resident Table

Order By Header_ID, Status,  SEQ_NUM  asc;

Thank you so much Sunny and Edwin for the response.