10 Replies Latest reply: Nov 2, 2017 8:30 AM by Sunny Talwar RSS

    Firstsortedvalue issue

    Matias Nogler

      I've read a lot about the function firstsortedvalue() but do not get the expected result.

       

      I have a table with 4 fields.

      - PrognoseID (1-150)

      - Article (numbers)

      - Flag A: letters

      - Flag B: letters

       

      Example

          

      ArticlePrognoseIDFlagAFlagB
      14417128ca
      1441792ca
      1441781ca
      14470135oa
      14470119oa
      1447087oa

       

       

      Some Articles are in different IDs with different Flags.

       

      My goal is to have Flag A and B for each Article based on the highest ID number. For Article 14417 this would be c & a from PrognoseID 128.

       

      For some reason, my ressult is blanc entries for Flag A & B for most of the articles. I hope one of you can help me.

       

      Below the script;

       

      Sortiment_Temp:

      LOAD PrognoseID,

           Article,

           FlagA,

           FlagB

          

      FROM

      [..\03_Datasource\Sortiment.qvd]

      (qvd) ;

       

      NoConcatenate

       

      Sortiment:

      Load

      max(PrognoseID) as PrognoseID,

           Article,

           FirstSortedValue(FlagA,PrognoseID) as FlagA

           FirstSortedValue(FlagB,PrognoseID) as FlagA

       

      Resident Sortiment_Temp

       

      Group by Article