4 Replies Latest reply: Aug 13, 2017 11:25 PM by Jessie Carl Mabalhin RSS

    Complicated calculated fields

    Jessie Carl Mabalhin
      LocationNameColumn1Column2First Order
      Orange - Services NSW (0088)221
      Botany Services NSW (RTA-9315)331
      Nowra Motor Registry (0086)441
      Chatswood Motor Registry (1318-0024)551
      Beverly Hills Motor Registry661
      Five  Dock Motor Registry (RTA-0038)771
      Miranda - Services NSW (5360)881
      CASTLE HILL Services NSW (RTA-9273)991
      Toukley Services NSW (RTA-0351)10101
      Newcastle - Services NSW (1511)11111
      Charlestown Motor Registry 12121
      Lidcombe Motor Registry (RTA-0057)13131
      MARRICKVILLE Services NSW (RTA-0066)14141
      Ryde Services NSW (1525)15151
      Toronto Motor Registry (0108)16161
      Bondi Services NSW (RTA-9314)17171
      Botany Services NSW (RTA-9315)1830
      Muswellbrook Motor Registry (0076)19191
      Wynyard Services NSW (1509)20201
      Miranda - Services NSW (5360)2180
      Tamworth Motor Registry 22221

       

      How can I handle this scenario?

       

      Column1 - is a calculated field that puts the row number as the value.

      Column2 - is another calculated field that puts the row number of the first instance the LocationName.

                       Take row 18 'Botany Services NSW (RTA-9314)', the first instance can be found on row 3. That is why Column2 is set with 3.

      FirstOrder - is another calculated field based on Column1 and Column2.

       

      What would be the best approach for this?

       

      P.S. - there are more fields before LocationName, I just trimmed it down and pasted it here.

             - I am trying to convert something from Excel into Qlik so all the functions used for Column1, Column2, and FirstOrder are Excel-based.