Previous()

[#1]

I have a piece of script that looks at the values of a table, these values are completely random and have no set sequecne.

For example

Col A          Col B     Line_Number

45456464     8               1

45456464     45              2

45456464     21              3

102154          5               1

102154          3               2

324549          8               2

7878454         9               1

7878454         2               2

So the line number works fine when there is multiple occurances it resets to one which is great, however if you look at Col A value 324549  the line_number goes to 2.

How can I get the Line_number to reset to 1 in this instance while ensuring all other line_numbers are calculated as per above.

if(Previous(col_A)=col_A,numsum(peek('Line_Number'),1),1) As Line_Number

Thanks,

Aidan

[anbu1984]

Your code works correctly.

Prev:

Load *,if(Previous(col_A)=col_A,numsum(peek('Line_Number'),1),1) As Line_Number1 Inline [

col_A,col_B,Line_Number

45456464,8,1

45456464,45,2

45456464,21,3

102154,5,1

102154,3,2

324549,8,2

7878454,9,1

7878454,2,2 ];

[MK_QSL]

Temp:

Load * Inline

[

  Col A,        Col B

  45456464,     8

  45456464,     45

  45456464,     21

  102154,       5              

  102154,       3              

  324549,       8              

  7878454,      9              

  7878454,      2              

];

Final:

Load

  [Col A],

  [Col B],

  IF([Col A] = Previous([Col A]), RangeSum(Peek('Line_Number'),1),1) as Line_Number

Resident Temp

Order By [Col A], [Col B];

Drop Table Temp;

[rubenmarin]

Try using peek.

Maybe, if you have a 'where' or 'if' condition, previous will take the descarted register from source table, so it counts there is one previous register where Col_A='324549'.

if(Peek('col_A')=col_A,numsum(peek('Line_Number'),1),1) As Line_Number

[tresesco · Accepted solution]

Much simpler:

Load

          AutoNumber(RecNo(), ColA) as LineNo

[maxgro]

load

    ...,

  if([Col A]=peek([Col A]), peek(Line_Number)+1, 1 ) as Line_Number

Resident ......

Order By [Col A], [Col B];

[#7]

Thank you Massimo simple soultion in the end:)

[#8]

Thanks tresesco worked great