Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
qlikmeplease
Contributor III

Autonumber, adding numbers to Rows etc

Hi,

I have the below field containing the below data;

156489

156489

156489

156789

156789

156789

156789

156789

158978

I need the script to generate a number for each order number, but start a new sequence once it reaches a new Order.

Example below:

  

WHOSequence
156489 1
1564892
1564893
1567891
1567892
1567893
1567894
1567895
1589781

Please can someone assist?

Thanks in Advance

6 Replies

Re: Autonumber, adding numbers to Rows etc

May be this:

LOAD WHO,

     AutoNumber(RowNo(), WHO) as Sequence

FROM ....;

Re: Autonumber, adding numbers to Rows etc

May be use this?

LOAD *, AutoNumber(rowno(),FieldName) as Row Inline [

FieldName

156489

156489

156489

156789

156789

156789

156789

156789

158978

];

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Autonumber, adding numbers to Rows etc

Or this:

Table:

LOAD WHO

FROM....

FinalTable:

LOAD WHO,

     If(WHO = Previous(WHO), RangeSum(Peek('Sequence'), 1), 1) as Sequence

Resident Table

Order by WHO;

This option will give better performance for a larger dataset

ahaahaaha
Honored Contributor

Re: Autonumber, adding numbers to Rows etc

Hi Ruan,

Table1:

LOAD*Inline

[WHO

156489

156489

156489

156789

156789

156789

156789

156789

158978

];

Left Join

LOAD*,

If(Previous(WHO) = WHO, Rangesum(1, peek('Sequence')), 1) as Sequence

Resident Table1;

Result

10.jpg

Regards,

Andrey

qlikmeplease
Contributor III

Re: Autonumber, adding numbers to Rows etc

Thanks This worked ! You Champion

Re: Autonumber, adding numbers to Rows etc

Good, You may close this thread by flag Correct answer. Hope Sunny's 2 solutions should work. But, I don't know how your data seems. Any how some what Left Join works for you

Close this thread, Please

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)