Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Transaction No. in a Table

Dear All,

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

DateACCNo.
3/22/2013123
3/22/20131
3/22/20132
3/23/2013123
3/23/2013125
3/23/2013127
3/23/20131
3/23/2013126
3/25/2013123
3/25/2013124
3/25/20132
3/25/20133
3/30/20133
3/30/2013124

I want the output as below...

Create DateMobile No.Transaction#
3/22/20131231
3/22/201311
3/22/201321
3/23/20131232
3/23/20131251
3/23/20131271
3/23/201312
3/23/20131261
3/25/20131233
3/25/20131241
3/25/201322
3/25/201331
3/30/201332
3/30/20131242

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

1 Solution

Accepted Solutions

Re: Transaction No. in a Table

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

4 Replies
sushil353
Honored Contributor II

Re: Transaction No. in a Table

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

Not applicable

Re: Transaction No. in a Table

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#
1111
1121
1131
1112

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

Customer No.Transaction#
1111
1121
1131
1112
1113
1122

Re: Transaction No. in a Table

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

Not applicable

Re: Transaction No. in a Table

Thanks alot... it really helped..

Cheers!!!!

Community Browser