8 Replies Latest reply: Nov 20, 2017 5:55 AM by beck bakytbek RSS

    FirstSortedValue and Inner Join

    beck bakytbek

      Hi Folks,

       

      i got a question, i am using the firstsortedvalue () - function in script-area to show last value and it does work well

      my table does look like:

       

      id, name, city, id_date, amount

      James, Munich, 09.09.2017, 3

      John, Cologne, 12.03.2017, 23

       

      but if i add an additional value with the same city, for instance: Cologne, instead of the last value: 25.12.2017 i got the following table:

       

      id, name, city, id_date, amount

      James, Munich, 09.09.2017, 3

      John, Cologne, 12.03.2017, 23

      John, Cologne, 25.12.2017, 12


      my script -area does look like:


      test1:

      LOAD id,

          name,

          city,

          id_date,

          amount

      FROM

      ApplyMap.xlsx

      (ooxml, embedded labels, table is FirstSortedValue);

       

       

      Inner Join(test1)

      load

      id,

      FirstSortedValue(distinct city, -id_date,1) as city

      Resident test1

      Group By id;



      i want to achieve thies result:


      id, name, city, id_date, amount

      James, Munich, 09.09.2017,  3

      John, Cologne, 25.12.2017, 12



      Did i make any mistakes or am i missing something?


      Thanks a lot for any feedback

      Beck