Transaction No. in a Table

[#1]

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

[sushil353]

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

[#3]

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

[CELAMBARASAN · Accepted solution]

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

[#5]

Thanks alot... it really helped..

Cheers!!!!