Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

justinmethode
New Contributor

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

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.

Untitled.png

1 Solution

Accepted Solutions
MVP
MVP

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

Try this

LOAD time,

     barcode,

     AutoNumber(RowNo(), barcode) as pass#

FROM ...

3 Replies
MVP
MVP

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

Try this

LOAD time,

     barcode,

     AutoNumber(RowNo(), barcode) as pass#

FROM ...

MVP
MVP

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

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

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

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:

Capture.PNG