Forum Discussion

Rajan244's avatar
Rajan244
Helper I
3 years ago

Make duplicate column values to unique based the another two columns

Sample data:

 No Container_No Events Lat Lon
1 HLXU111 Dub-Rot 53.33306 -6.24889
1 HLXU111 Rot-Kara 51.9225 4.47917
1 HLXU222 Rot-Kara 51.225 4.7917
2 HLXU111 Dub-Rot 52.84235105 -6.16893985
2 HLXU111 Rot-Kara 51.79675051 4.429912263
2 HLXU222 Rot-Kara 51.925 4.4917
3 HLXU111 Dub-Rot 50.55237595 -5.79583915
3 HLXU111 Rot-Kara 51.533278 3.430639
3 HLXU222 Rot-Kara 51.922 4.4791
4 HLXU111 Dub-Rot 50.061667 -5.715889
4 HLXU111 Rot-Kara 49.727861 -1.940028
4 HLXU222 Rot-Kara 49.72786 -1.94002
5 HLXU111 Dub-Rot 50.08522105 -5.4050098

 Desire Data:

No  Container_No    Events  Lat Lon

1.1 HLXU111 Dub-Rot 53.33306    -6.24889
2.1 HLXU111 Rot-Kara    51.9225 4.47917
3.1 HLXU222 Rot-Kara    51.225  4.7917
1.2 HLXU111 Dub-Rot 52.84235105 -6.16893985
2.2 HLXU111 Rot-Kara    51.79675051 4.429912263
3.2 HLXU222 Rot-Kara    51.925  4.4917
1.3 HLXU111 Dub-Rot 50.55237595 -5.79583915
2.3 HLXU111 Rot-Kara    51.533278   3.430639
3.3 HLXU222 Rot-Kara    51.922  4.4791
1.4 HLXU111 Dub-Rot 50.061667   -5.715889
2.4 HLXU111 Rot-Kara    49.727861   -1.940028
4.4 HLXU222 Rot-Kara    49.72786    -1.94002
1.5 HLXU111 Dub-Rot 50.08522105 -5.4050098

 

 

Need Help
Show and Tell
Tips and Tricks

2 Replies

    • Rajan244's avatar
      Rajan244
      Helper I
      3 years ago

      Hi amitchandak Thank you for your quick reply. Actually I forgot to mention, need to make unique based on Container_no, and events. 

      Also, if its possible can you please add littlebit explaination about query, so I could change by my dataset. Thanks again.

       

      Thanks amitchandak 

      Used:

       

      !no = RANKX(His_data, COMBINEVALUES(",", His_data[Container_No],COMBINEVALUES("-",[From],[To])),, ASC, DENSE) + ([no]/10)