create a count of how many times this serial has appeared during LOAD

[Anonymous]

Hello,

my source data has two columns, time and serial number/barcode. Time will always be different but the serial number may appear a number of times. What i need is to add a column that notes the number of times that that particular serial number has appeared. I am trying to post some sample images for illustration. Many thanks.

[sunny_talwar · Accepted solution]

Try this

LOAD time,

     barcode,

     AutoNumber(RowNo(), barcode) as pass#

FROM ...

[sunny_talwar]

Or this

Table:

LOAD time,

     barcode

FROM ...;

FinalTable:

LOAD *,

     If(barcode = Previous(barcode), RangeSum(Peek('pass#'), 1), 1) as [pass#]

Resident Table

Order By barcode, time;

DROP Table Table;

[OmarBenSalem]

try this:

table:

load * Inline [

time, barcode

06:04, 123

06:05,124

06:06,125

06:07,123

06:08,123

];

NoConcatenate

load *, if(len(trim(peek(barcode)))=0,1, if(Peek(barcode)<>barcode,1,rangesum(Peek(flag)+1))) as flag;

load * Resident table Order by barcode;

drop Table table;

result: