Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Ahh, alright.
LOAD Header_ID,
SEQ_NUM,
Status,
AutoNumber(RowNo(),Header_ID&Status) as Result
FROM [...]
Try this again.
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;
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.
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.
Ahh, alright.
LOAD Header_ID,
SEQ_NUM,
Status,
AutoNumber(RowNo(),Header_ID&Status) as Result
FROM [...]
Try this again.
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.
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
];
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;
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.
Thanks Sunny, Finally succeed. Just i put SEQ_NUM asc because i need result based on seq_num also.