Table serial no based on one column

[Sudheeshp]

in table [Transaction Time], [Trx ID] and [U PAX].

[U PAX] is repetative, we like to put serial no for each [U PAX] based on [Transaction Time].  Please enlighten

[rubayatyasmin · Accepted solution]

Hi, @Sudheeshp 

To achieve your goal you need to use "Index Column" in the Transaction time column and then use group by for the U PAX column. Below are the steps you might want to follow:-

1. First, ensure your Transaction Time is in the correct data type. If not, you can convert it by selecting the column > Transform > Data Type > Date/Time.

2. Next, sort the Transaction Time column in ascending order by selecting the column > Home > Sort Ascending.

3. Now, it's time to create a Serial No. column. Go to Add Column > Index Column > From 1. This will add a new column to the table which acts as a serial number, incrementing by one for each row.

4. This will, however, number all rows consecutively. If you need a reset of the serial number for each U PAX, you need to create a grouped index. First, we need to group the rows. Go to Transform > Group By. A new window opens. In the New column name field enter "Temp", for Operation select "All Rows".

5. Expand the created Temp column by clicking on the icon with two opposing arrows in the header of the Temp column. In the appearing menu uncheck Use original column name as prefix and confirm by clicking OK.

6. Now we need to create an index per group. Go to Add Column > Index Column > From 1. This will add a column with an index starting from 1 for each U PAX.

7. Finally, you may want to rename and reorder the columns to your liking, and remove any temporary columns you've added during these steps that you don't need.
   
   
   Did you find the answer helpful? If so, then hit 👍 and "Accept the answer as solution" so that others find it helpful. 
   
   Thanks