How to generate a Version Number based on a Key

Follow

Saurabh Agashe

• April 16, 2015 18:08

Hi

I have to generate record version number for a naturalkey(id) based on change_datetime. Below is the sample of input data and expected output.

Ive sorted results on ID and changedatetime and in a separate worksheet Ive grouped on Id and used grouprownumber but not getting consistent results. grouprownumber is generating unique number but looks like its not considering the change datetime to generate number in sequence.

thanks for the help.

Input:
Id  ChangeDateTime
1   03/03/2015 06:00:00 AM
1   03/03/2015 05:00:00 AM
2   03/03/2015 06:00:00 AM
2   03/03/2015 07:30:00 AM
2   03/03/2015 06:30:00 AM

Output:
Id  ChangeDateTime        versionNumber
1   03/03/2015 06:00:00 AM  2
1   03/03/2015 05:00:00 AM  1
2   03/03/2015 06:00:00 AM  1
2   03/03/2015 07:30:00 AM  3
2   03/03/2015 06:30:00 AM  2

0

• Official comment

  

  Saurabh Agashe

  • April 16, 2015 18:09

  With your example data proceed like this:

  Let's name the sheet, which contains your input, Data.

  Create a new sheet, and on the first column GROUPBY(#Data!Id). On the second column use the function GROUPCONCAT(#Data!ChangeDateTime;#Data!ChangeDateTime) This will create a list with the elements corresponding to each Id group sorted by DateTime. Rename this sheet to ListedData

  Create a new sheet and on the first column GROUPBY(#ListedData!Id)

  On the second column use the function EXPAND(#ListedData!ChangeDateTime) This function will kind of re-generate the input list, but sorted by Id and by DateTime.

  On the third column use the function INDEXOF(#ListedData!ChangeDateTime;#ChangeDateTime)+1 This function uses very conveniently the index of the list we created on the sheet ListedData to enumerate the changes sorted by DateTime (the order in which the list was ordered with the GROUPCONCAT() function).

  Whereas sorting the columns Id and ChangeDateTime could have been done by sorting them, the creation of an intermediate list, give us access to the list indexes. These indexed can then be used to create the versionNumber values, which you are looking for.

  Comment actions Permalink
• 

  Saurabh Agashe

  • April 16, 2015 18:09

  Brilliant!

  Thanks.

  0

  Comment actions Permalink

Please sign in to leave a comment.