Scripting: Numbering rows within subsets of data

[ccdqlik1]

Dear Qlik Community,

There's probably an easy solution for this, but...

I'm loading data from different sources and concatenating them together.  After the load/concatenate, I want to add a sequence number by subsets of the data, not overall across the entire data set. I want to do this in the loader script so the sequence number is a permanent column/field in the table.  I tried autonumber and RowNo(), but they don't go far enough and return row numbers across the entire data set.  I'm thinking I might need to add some kind of set analysis or go a different route.

Here's the simple example  I want to number each customer/item across the order dates:

AllOrders:

     Load CustomerNumber, ItemNumber, OrderDate From OrderHistory.QVD;

Contatenate (AllOrders)

     Load CustomerNumber, ItemNumber, OrderDate From CurrentOrders.QVD;

(Below, CN is CustomerNumber, I is ItemNumber, Date is Order Date)

I'm looking for this:

CN I Date       Seq

91 a 10/1/15    1

91 b 12/1/15    1

92 a 5/4/14     1

92 a 4/5/15     2

92 c 4/5/15     1

93 d 1/14/16    1

94 a 5/6/16     1

94 a 6/6/16     2

94 a 7/6/16     3

Not this:

CN I Date       Seq

91 a 10/1/15    1

91 b 12/1/15    2

92 a 5/4/14     3

92 a 4/5/15     4

92 c 4/5/15     5

93 d 1/14/16    6

94 a 5/6/16     7

94 a 6/6/16     8

94 a 7/6/16     9

Can someone please point me in the right direction?

Thanks.

[andrey_krylov]

Hi, Celeste. Try this code

LOAD CN, I, Date,

          If(CN= Previous(CN) and I= Previous(I), RangeSum(Peek('Seq'), 1), 1) as [Seq]

Resident YouTable

Order By CN, I, Date;

[neelamsaroha157 · Accepted solution]

You can also try this -

if(CN= Previous(CN) , Peek(FieldEncounter)+1, 1) as FieldEncounter

and in the end use order by statement as -

Order by CN, Date Acs;

[neelamsaroha157]

I am not sure if you want to want the same number for the field 'I' as well, if yes, then you can add 'and' condition like Andrey mentioned.

[MarcoWedel]

Hi,

one solution might be also:

table1:
LOAD *,
    AutoNumber(RowNo(),CN&'/'&I) as Seq
Inline [
    CN I Date
    91 a 10/1/15
    91 b 12/1/15
    92 a 5/4/14
    92 a 4/5/15
    92 c 4/5/15
    93 d 1/14/16
    94 a 5/6/16
    94 a 6/6/16
    94 a 7/6/16
] (delimiter is spaces);

hope this helps

regards

Marco

[ccdqlik1]

Thank you.  This worked.

[ccdqlik1]

Thank you for your reply.  Rangesum will be useful for another report I'm working on.

[ccdqlik1]

Thank you for your reply.