Version Number

[#1]

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

[kaushiknsolanki]

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

[jonathandienst · Accepted solution]

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

[rwunderlich]

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

[#5]

Thanks Guys

Think I have it going on