topic Transaction No. in a Table in QlikView

Transaction No. in a Table

Fri, 22 Mar 2013 12:08:25 GMT

Dear All,

Need a small help... I have a table as Below

Date	ACCNo.
3/22/2013	123
3/22/2013	1
3/22/2013	2
3/23/2013	123
3/23/2013	125
3/23/2013	127
3/23/2013	1
3/23/2013	126
3/25/2013	123
3/25/2013	124
3/25/2013	2
3/25/2013	3
3/30/2013	3
3/30/2013	124

I want the output as below...

Create Date	Mobile No.	Transaction#
3/22/2013	123	1
3/22/2013	1	1
3/22/2013	2	1
3/23/2013	123	2
3/23/2013	125	1
3/23/2013	127	1
3/23/2013	1	2
3/23/2013	126	1
3/25/2013	123	3
3/25/2013	124	1
3/25/2013	2	2
3/25/2013	3	1
3/30/2013	3	2
3/30/2013	124	2

I can get this Information whith the help of below formula in excel....

=COUNTIF($B$2:B3,B3)

please help if can can do the same in Qlikview.

Thanks in advance...

Regards,

Nitesh Srivastava

Re: Transaction No. in a Table

sushil353 — Fri, 22 Mar 2013 12:11:11 GMT

Can you explain the logic to get the transaction#. sorry i am not good in excel functions

Re: Transaction No. in a Table

Fri, 22 Mar 2013 14:33:51 GMT

Hi Shushil,

Thanks for the response...

I would like to explain the requirement...

Lets assume I have a Table...

Customer No.

111

112

113

111

Now my requirement in this table '111' is repeated twice in table...

So when 111 come first in the table, Transaction# will be 1, second time it will be 2, thrird time it will be 3 so on and so forth...

Customer No.	Transaction#
111	1
112	1
113	1
111	2

This logic should apply on all the values in 'Customer No.'

Customer No.	Transaction#
111	1
112	1
113	1
111	2
111	3
112	2

Re: Transaction No. in a Table

CELAMBARASAN — Fri, 22 Mar 2013 14:49:53 GMT

Hi,

First order the table based on the customer number and use Previous function to achieve that.

Temp:

Load

Date,

CustomerNo

From SourceTable

Order by CustomerNo;

Transactions:

Load

Date,

CustomerNo,

If(Previous(CustomerNo)=CustomerNo, Peek('Transaction#') + 1, 1) As Transaction#

From Temp;

Drop Table Temp;

Hope it helps

Celambarasan

Re: Transaction No. in a Table

Fri, 22 Mar 2013 15:06:29 GMT

Thanks alot... it really helped..

Cheers!!!!