topic Generate unique number based on condition in Management & Governance

Generate unique number based on condition

rkpatelqlikview — Sat, 16 Nov 2024 05:36:11 GMT

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.

Re: Generate unique number based on condition

Edvin — Thu, 13 Jun 2019 10:54:04 GMT

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.

Re: Generate unique number based on condition

rkpatelqlikview — Thu, 13 Jun 2019 11:03:10 GMT

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.

Re: Generate unique number based on condition

Edvin — Thu, 13 Jun 2019 11:12:15 GMT

Ahh, alright.

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

Try this again.

Re: Generate unique number based on condition

rkpatelqlikview — Thu, 13 Jun 2019 11:51:51 GMT

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.

Re: Generate unique number based on condition

Edvin — Thu, 13 Jun 2019 11:59:23 GMT

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
];

Re: Generate unique number based on condition

sunny_talwar — Thu, 13 Jun 2019 12:01:31 GMT

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;

Re: Generate unique number based on condition

rkpatelqlikview — Thu, 13 Jun 2019 12:02:02 GMT

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.

Re: Generate unique number based on condition

rkpatelqlikview — Thu, 13 Jun 2019 12:14:23 GMT

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.