Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
];
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
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
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
Hi Bob ,
Counter Perfomance is very slow. Is there any other alternative?
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