topic Re: Re: Create Sequence Number in QlikView

Create Sequence Number

Anonymous — Fri, 05 Dec 2014 10:04:35 GMT

Hi,

I would like to add a grouped sequence number in the QlikView straight table and need some help.

I have the column A to F shown in the dimensions of a straight table and want to add a running sequence number expression that groups on only column A to C.

Please see the attached excel where I have manually added the sequence column. This column should not be there and should be generated in QlikView.

Please help. Thx!

Re: Create Sequence Number

sushil353 — Fri, 05 Dec 2014 10:13:39 GMT

Hi,

Try below code:

AutoNumber(rowno(),SAC.RouteDay) as Sequence

HTH

Sushil

Re: Create Sequence Number

tresB — Fri, 05 Dec 2014 10:13:53 GMT

In the script, try like:

Load

AutoNumber(RowNo(),WeekDayNo) as Sequence

From <>;

Re: Create Sequence Number

anbu1984 — Fri, 05 Dec 2014 10:18:14 GMT

Load *,AutoNumber(RecNo(),SAC.AddressNumberSalesStaff&WeekNo&WeekDayNo) As NewSeq;

LOAD SAC.AddressNumberSalesStaff,

WeekNo,

WeekDayNo,

SAC.AddressNumberCustomer,

SAC.CustomerName,

SAC.RouteDay,

Sequence

FROM

(biff, embedded labels, table is Sheet1$);

Re: Create Sequence Number

its_anandrjs — Fri, 05 Dec 2014 10:18:29 GMT

You can try to add like

AutoNumber(rowno(),SAC.RouteDay) as Sequence

AutoNumber(rowno(),WeekDayNo&SAC.RouteDay) as Sequence

Regards

Anand

Re: Create Sequence Number

MarcoWedel — Fri, 05 Dec 2014 10:26:55 GMT

LOAD *,

AutoNumber(RecNo(),AutonumberHash128(SAC.AddressNumberSalesStaff,WeekNo,WeekDayNo)) as Sequence

FROM yoursource;

Re: Create Sequence Number

Anonymous — Mon, 08 Dec 2014 01:17:42 GMT

Thx all for the script! It works but I would like to be done in the expression of a straight table and there does not seem to have Autonumber function in expression.

I need it done in the expression because the user may filter the result and the expression can dynamically generate the sequence number.

Can advise further anyone?

Re: Create Sequence Number

nagaiank — Mon, 08 Dec 2014 01:40:32 GMT

The following script also works. If you want to implement Seq as chart expression, remove the preceding load in the script, sort the straight table by ID ascending and use the expression

If(Peek(SAC.RouteDay)=SAC.RouteDay,Peek(Seq)+1,1)

for your new sequence number column.

Data:

LOAD *,If(Peek(SAC.RouteDay)=SAC.RouteDay,Peek(Seq)+1,1) as Seq;

LOAD SAC.AddressNumberSalesStaff,

WeekNo,

WeekDayNo,

SAC.AddressNumberCustomer,

SAC.CustomerName,

SAC.RouteDay,

Sequence,

RecNo() as ID

FROM

(biff, embedded labels, table is Sheet1$);

Re: Create Sequence Number

Anonymous — Mon, 08 Dec 2014 01:50:44 GMT

Hi NagaianK,

Just tried in expression but Peek does not seem to be recognized in expression. Are you able to provide an example in qlikview?

Re: Re: Create Sequence Number

nagaiank — Mon, 08 Dec 2014 02:41:42 GMT

Yes. You are correct. Sorry for giving an incorrect expression. Peek does not work in chart expressions. You should use 'Above' instead. An example is attached. Hope this helps.

Re: Create Sequence Number

Anonymous — Thu, 11 Dec 2014 01:24:31 GMT

Thx all... you have been a great help!

Nagaian,

I was wondering, without the ID column in the test, how would the solution work?