Qlik Community

Qlik Sense Deployment & Management

Discussion board where members learn more about Qlik Sense Installation, Deployment and Management.

Highlighted
rkpatelqlikview
Contributor III

Generate unique number based on condition

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'.

 

seq.PNG

Thanks in advance. 

Labels (1)
2 Solutions

Accepted Solutions
Edvin
New Contributor III

Re: Generate unique number based on condition

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

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;
8 Replies
Edvin
New Contributor III

Re: Generate unique number based on condition

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
Contributor III

Re: Generate unique number based on condition

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
New Contributor III

Re: Generate unique number based on condition

Ahh, alright.


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

Try this again.

rkpatelqlikview
Contributor III

Re: Generate unique number based on condition

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. 

 

safd.PNG

Edvin
New Contributor III

Re: Generate unique number based on condition

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

 

example.png

Re: Generate unique number based on condition

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
Contributor III

Re: Generate unique number based on condition

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
Contributor III

Re: Generate unique number based on condition

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.