topic Re: Autonumber, adding numbers to Rows etc in QlikView

Autonumber, adding numbers to Rows etc

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have the below field containing the below data;

156489

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:

WHO	Sequence
156489	1
156489	2
156489	3
156789	1
156789	2
156789	3
156789	4
156789	5
158978	1

Please can someone assist?

Thanks in Advance

Re: Autonumber, adding numbers to Rows etc

sunny_talwar — Fri, 03 Feb 2017 12:29:49 GMT

May be this:

LOAD WHO,

AutoNumber(RowNo(), WHO) as Sequence

FROM ....;

Re: Autonumber, adding numbers to Rows etc

Anil_Babu_Samineni — Fri, 03 Feb 2017 12:30:33 GMT

May be use this?

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

FieldName

156489

156789

158978

];

Re: Autonumber, adding numbers to Rows etc

sunny_talwar — Fri, 03 Feb 2017 12:31:30 GMT

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

Re: Autonumber, adding numbers to Rows etc

ahaahaaha — Fri, 03 Feb 2017 12:36:14 GMT

Hi Ruan,

Table1:

LOAD*Inline

[WHO

156489

156789

158978

];

Left Join

LOAD*,

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

Resident Table1;

Result

Regards,

Andrey

Re: Autonumber, adding numbers to Rows etc

Anonymous — Fri, 03 Feb 2017 12:52:42 GMT

Thanks This worked ! You Champion

Re: Autonumber, adding numbers to Rows etc

Anil_Babu_Samineni — Fri, 03 Feb 2017 13:06:35 GMT

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