Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
(I have touched on this as part of another thread also - Distinct compare 2 diff tables in load)
Quick one, I have a table that will have duplicate entries within 1 of the fields, what I wish to do is count what version it is so I may concatenate it together to ensure I only compare correct timeseriesed accounts - see tables below please
Original :
Account Version Number
1234 1
1111 1
2222 1
1234 2
1111 2
1234 3
2222 2
If I was doing this in excel I would count(A$2:A2,A2) so I would count from the start to where I am now then Id concatenate account and Version the desired result is below ( I know theres a recno() function, is there an equivalent verno(Account) similar)
Desired result:
Account Version Number Search
1234 1 12341
1111 1 11111
2222 1 22221
1234 2 12342
1111 2 11112
1234 3 12342
2222 2 22222
Can anyone help please ?
Thanks
A
Hi
Please check out the attached file. Does it do what you want?
// Load source data. RowNo is used to maintain original load order
Data:
LOAD
Account,
RowNo() As RowNo
INLINE
[
Account
1234
1111
2222
1234
1111
1234
2222
];
// Calculate the sequence number by looping over the data and incrementing
// the sequence number for successive accounts with the same value
Sequence:
LOAD *,
Account & Sequence As SearchNo
;
LOAD Account,
RowNo,
If(Account = Previous(Account), Peek('Sequence') + 1, 1) As Sequence
Resident Data
Order By Account, RowNo;
DROP Table Data;
Regards
Jonathan
Hi,
Can you please tell us the logic..
Cause in your example only Accout which has version number 3 is concated with older version.
So pls tell whats the logic.
Regards,
Kaushik Solanki
Hi
Please check out the attached file. Does it do what you want?
// Load source data. RowNo is used to maintain original load order
Data:
LOAD
Account,
RowNo() As RowNo
INLINE
[
Account
1234
1111
2222
1234
1111
1234
2222
];
// Calculate the sequence number by looping over the data and incrementing
// the sequence number for successive accounts with the same value
Sequence:
LOAD *,
Account & Sequence As SearchNo
;
LOAD Account,
RowNo,
If(Account = Previous(Account), Peek('Sequence') + 1, 1) As Sequence
Resident Data
Order By Account, RowNo;
DROP Table Data;
Regards
Jonathan
If you want to add sequence numbers to the accounts, I find the easiest way is to add this field in the load:
autonumber(recno(), Account) as [Version Number]
-Rob
Thanks Guys
Think I have it going on