topic Scripting: Numbering rows within subsets of data in QlikView

Scripting: Numbering rows within subsets of data

ccdqlik1 — Fri, 07 Sep 2018 16:07:42 GMT

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.

Re: Scripting: Numbering rows within subsets of data

andrey_krylov — Fri, 07 Sep 2018 17:34:17 GMT

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;

Re: Scripting: Numbering rows within subsets of data

neelamsaroha157 — Fri, 07 Sep 2018 17:57:47 GMT

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;

Re: Scripting: Numbering rows within subsets of data

neelamsaroha157 — Fri, 07 Sep 2018 17:59:26 GMT

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.

Re: Scripting: Numbering rows within subsets of data

MarcoWedel — Fri, 07 Sep 2018 21:14:59 GMT

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

Re: Scripting: Numbering rows within subsets of data

ccdqlik1 — Mon, 10 Sep 2018 13:03:10 GMT

Thank you. This worked.

Re: Scripting: Numbering rows within subsets of data

ccdqlik1 — Mon, 10 Sep 2018 13:04:56 GMT

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

Re: Scripting: Numbering rows within subsets of data

ccdqlik1 — Mon, 10 Sep 2018 13:05:54 GMT

Thank you for your reply.