topic Re: How to do an order counter id in QlikView

How to do an order counter id

ecabanas — Tue, 13 Oct 2015 16:57:52 GMT

Hi guys

I have a table with the CUSTACCOUNT, SALESID and CREATEDDATETIME. In the master table obviously I have several CUTACCOUNT and SALESID, but I would like to "numerate" the orders, in order to know Frequency, etc etc. as in the following table

Many many thanks

Eduard

CUSTACCOUNT	SALESID	CREATEDDATETIME	Order Number
C000002	PV00161412	16/09/2011	1
C000002	PV00161447	19/09/2011	2
C000002	PV00161459	20/09/2011	3
C000002	PV90000115	17/10/2011	4
C000002	PV00162773	28/12/2011	5
C000002	PV90000251	28/12/2011	6
C000002	PV00163615	10/02/2012	7
C000002	PV00167426	02/08/2012	8
C000002	PV00167833	17/08/2012	9
C000002	PV00172148	26/10/2012	10
C000002	PV00175058	12/12/2012	11
C000002	PV00175672	22/12/2012	12
C000002	PV00176305	04/01/2013	13
C000002	PV00177766	25/01/2013	14
C000002	PV00179238	15/02/2013	15
C000002	PV00181063	13/03/2013	16
C000002	PV00182098	01/04/2013	17
C000002	PV00183345	19/04/2013	18
C000002	PV00184958	13/05/2013	19
C000002	PV00186269	30/05/2013	20
C000002	PV00189628	04/07/2013	21
C000002	PV00192653	13/08/2013	22
C000002	PV00195781	16/09/2013	23

Re: How to do an order counter id

sunny_talwar — Tue, 13 Oct 2015 17:02:47 GMT

May be something like this:

LOAD CUSTACCOUNT,

SALESID,

CREATEDDATETIME,

RowNo() as [Order Number]

FROM Source

Re: How to do an order counter id

nagaiank — Tue, 13 Oct 2015 17:04:03 GMT

You may use RowNo() as OrderNumber.

E.g LOAD RowNo() as OrderNumber,

CUSTACCOUNT,

SALESID,

CREATEDATETIME

From (Your Source)

Re: How to do an order counter id

ecabanas — Tue, 13 Oct 2015 17:05:02 GMT

Hi Sunny,

The problem is that the source table is not ordered is like this:

CUSTACCOUNT	SALESID	CREATEDDATETIME
C000015	PV00161133	03/09/2011
C000002	PV00161412	16/09/2011
C000002	PV00161447	19/09/2011
C000002	PV00161459	20/09/2011
C000014	PV00161584	29/09/2011
C000003	PV00161731	11/10/2011
C000015	PV00161809	15/10/2011
C000002	PV90000115	17/10/2011
C000003	PV00161957	27/10/2011
C000015	PV00162157	10/11/2011
C000003	PV00162259	18/11/2011
C000014	PV00162260	18/11/2011
C000014	PV00162273	20/11/2011
C000003	PV90000203	13/12/2011
C000002	PV00162773	28/12/2011
C000002	PV90000251	28/12/2011
C000014	PV00163297	22/01/2012
C000002	PV00163615	10/02/2012
C000015	PV00163953	26/02/2012
C000005	PV00164332	22/03/2012
C000015	PV00164457	27/03/2012
C000014	PV00164542	01/04/2012
C000015	PV00164995	01/05/2012
C000014	PV00165198	11/05/2012
C000014	PV00165341	17/05/2012

Re: How to do an order counter id

sunny_talwar — Tue, 13 Oct 2015 17:08:35 GMT

Try this:

Table:

LOAD CUSTACCOUNT,

SALESID,

CREATEDDATETIME

FROM

[https://community.qlik.com/thread/186667]

(html, codepage is 1252, embedded labels, table is @2);

FinalTable:

LOAD *,

RowNo() as [Order Number]

Resident Table

Order By CUSTACCOUNT, SALESID;

DROP Table Table;

Re: How to do an order counter id

maxgro — Tue, 13 Oct 2015 17:10:28 GMT

A different one:

you can use (in a chart) a count of a not null field in the same table to know the number of rows.

Re: How to do an order counter id

sunny_talwar — Tue, 13 Oct 2015 17:14:09 GMT

Slight change in the script:

Table:

LOAD CUSTACCOUNT,

SALESID,

CREATEDDATETIME

FROM

[https://community.qlik.com/thread/186667]

(html, codepage is 1252, embedded labels, table is @2);

FinalTable:

LOAD *,

RowNo() as [Order Number]

Resident Table

Order By CUSTACCOUNT, CREATEDDATETIME;

DROP Table Table;

Output in Table Box:

Re: How to do an order counter id

ecabanas — Tue, 13 Oct 2015 17:29:27 GMT

Hi Sunny,

Where are going by the good track, but I want to know the counter per customer. For example in your table, when it changes from custaccount C00002 to C00003 the counter has to be reseted....I want to know the order number by customer

Many many thanks guys!!

Eduard

Re: How to do an order counter id

maxgro — Tue, 13 Oct 2015 17:43:09 GMT

load *

inline [

CUSTACCOUNT, SALESID, CREATEDDATETIME

C000015, PV00161133, 03/09/2011

C000002, PV00161412, 16/09/2011

C000002, PV00161447, 19/09/2011

C000002, PV00161459, 20/09/2011

C000015, PV00164457, 27/03/2012

C000014, PV00164542, 01/04/2012

C000015, PV00164995, 01/05/2012

C000014, PV00165198, 11/05/2012

C000014, PV00161584, 29/09/2011

C000003, PV00161731, 11/10/2011

C000015, PV00161809, 15/10/2011

C000002, PV90000115, 17/10/2011

C000003, PV00161957, 27/10/2011

C000015, PV00162157, 10/11/2011

C000003, PV00162259, 18/11/2011

C000014, PV00162260, 18/11/2011

C000014, PV00162273, 20/11/2011

C000003, PV90000203, 13/12/2011

C000002, PV00162773, 28/12/2011

C000002, PV90000251, 28/12/2011

C000014, PV00163297, 22/01/2012

C000002, PV00163615, 10/02/2012

C000015, PV00163953, 26/02/2012

C000005, PV00164332, 22/03/2012

C000014, PV00165341, 17/05/2012

] ;

NoConcatenate load *, AutoNumber(rowno(), CUSTACCOUNT) as id Resident x

order by CUSTACCOUNT, CREATEDDATETIME;

DROP Table x;

Re: How to do an order counter id

sunny_talwar — Tue, 13 Oct 2015 17:44:22 GMT

May be this:

Table:

LOAD CUSTACCOUNT,

SALESID,

CREATEDDATETIME

FROM

[https://community.qlik.com/thread/186667]

(html, codepage is 1252, embedded labels, table is @2);

FinalTable:

LOAD *,

If(Peek('CUSTACCOUNT') = CUSTACCOUNT, Peek('Order Number') + 1, 1) as [Order Number]

Resident Table

Order By CUSTACCOUNT, CREATEDDATETIME;

DROP Table Table;

Re: How to do an order counter id

ecabanas — Wed, 14 Oct 2015 07:45:25 GMT

Hi sunny,

Now The script is working EXCELLENT many many thank's, if you come to Barcelona you'll have a paella ready to eat 😉

Re: How to do an order counter id

sunny_talwar — Wed, 14 Oct 2015 11:35:33 GMT

Awesome, now I soon need to plan a trip to Barcelona then