Count for Each Record

[#1]

Hi All ,

TRN ID	Date	Time
123A	6/1/2015	10:00 AM
123A	6/1/2015	10:30 AM
145A	7/1/2015	11:00 AM
156A	7/1/2015	11:00 AM
156A	7/2/2015	10:00 AM

For the Above table ,

i need to add a counter for each TRN ID sorted based on Date and Time.

And the Counter will be used as a KEY for further calculations.

Final Expected Output

TRN ID	Date	Time	Counter
123A	6/1/2015	10:00 AM	1
123A	6/1/2015	10:30 AM	2
145A	7/1/2015	11:00 AM	1
156A	7/1/2015	11:00 AM	1
156A	7/2/2015	10:00 AM	2

Regards,

Senthil

[senpradip007]

Try this. Hope it will help.

LOAD *, if(TRN_ID <> Previous(TRN_ID), 1, Peek(Counter)+1) as Counter Inline [

TRN_ID, Date, Time

123A, 6/1/2015, 10:00 AM

123A, 6/1/2015, 10:30 AM

145A, 7/1/2015, 11:00 AM

156A, 7/1/2015, 11:00 AM

156A, 7/2/2015, 10:00 AM

];

[rwunderlich · Accepted solution]

Another approach is to use AutoNumber().

LOAD

  *,

  autonumber(recno(), TRN_ID) as Counter

Inline [

TRN_ID, Date, Time

123A, 6/1/2015, 10:00 AM

123A, 6/1/2015, 10:30 AM

145A, 7/1/2015, 11:00 AM

156A, 7/1/2015, 11:00 AM

156A, 7/2/2015, 10:00 AM

];

This approach is not dependent on the input being in TRN_ID order.

-Rob

http://masterssummit.com

http://robwunderlich.com

[#4]

Hi Rob,

Thanks, Counter is working fine.

If am loading data from 2 different tables, my counter is shared between both the tables.

Hence am not able to create a key using the counters in both the files.

Tabl1:

TRN	Counter	Rec in OFAC date	Received Time
20150309-00029814	1	150310	02:28:16
20150309-00029814	2	150401	16:19:02
20150312-00004425	1	150312	03:47:29
20150312-00004425	2	150401	14:20:03
20150317-00036485	1	150318	02:37:23
20150317-00036485	2	150401	04:47:10

Table 2:

TRN	Counter1	Real time	Release date
20150309-00029814	3	15:07:25	150401
20150309-00029814	4	15:28:30	150401
20150312-00004425	3	10:52:52	150401
20150312-00004425	4	13:50:40	150401
20150317-00036485	3	16:15:04	150331
20150317-00036485	4	5:51:01	150401

Counter in table 2 also should start with 1

[rwunderlich]

‌You can concatenate the filename with the tranid in the autonumber to get a unique count for each file.

autonumber(recno(), TRN_ID & filename())

-Rob

[#6]

Hi Bob ,

Counter Perfomance is very slow. Is there any other alternative?

[rwunderlich]

‌autonumber can be slow if you have many values for the second parameter. The previous/ peek method suggested by Pradip is much faster. But you do have to read the rows in order.

Rob