3 Replies Latest reply: Jun 24, 2014 2:12 PM by Srikanth P RSS

    Top 1 Entry; Order By Errors

    Jerome Jackson

       

      [Loss Mit Steps 999]:

       

       

      LOAD [Loan Number],
      [ Setup Date],
      [ Step Code],
      [Completion Date] as s367_Dt,
      [Record Change Code]
      FROM
      [S:\Data\QVD\LOSS_MIT_STEPS.qvd](
      qvd)
      where match([ Step Code],'367')

      and match([Record Change Code],'x','c')

      Order By  [Completion Date] desc ;

       

      I then want to choose the first instance only or the most recent by date.

      I researched where you can use First (1) [Completion Date]

      However when I run the above named code I keep getting garbage after error when I include the Order By statement.  When I exclude the Order By Statement, no problems.  Ultimatley I wish to get the top 1 record based on the Completion Date.  Any ideas here??

       

       

        • Re: Top 1 Entry; Order By Errors
          Gysbert Wassenaar

          You can only use Order By with a resident load. You're loading data from a qvd so you can't use Order By in that load statement.

           

          [Loss Mit Steps 999]:
          LOAD [Loan Number], 
          [ Setup Date], 
          [ Step Code], 
          [Completion Date] as s367_Dt,
          [Record Change Code]
          FROM
          [S:\Data\QVD\LOSS_MIT_STEPS.qvd](qvd)
          where match([ Step Code],'367')
          and match([Record Change Code],'x','c')
          
          Result:
          First 1
          noconcatenate Load * 
          resident [Loss Mit Steps 999]
          Order by  s367_Dt desc ;
          
          drop table [Loss Mit Steps 999];
          
          
          
          • Re: Top 1 Entry; Order By Errors

            Hi:

             

            Try this:

             

            [Loss Mit Steps 999]:

            LOAD

            FirstValue([Loan Number]) as [Loan Number],
            FirstValue([ Setup Date]) as [ Setup Date],
            FirstValue([ Step Code]) as [ Step Code],
            Max([Completion Date]) as s367_Dt,
            FirstValue([Record Change Code]) as [Record Change Code]
            FROM
            [S:\Data\QVD\LOSS_MIT_STEPS.qvd](qvd)
            where match([ Step Code],'367')

            and match([Record Change Code],'x','c')

            Order By  [Completion Date] desc ;

             

            Best regards.

            • Re: Top 1 Entry; Order By Errors
              Srikanth P

              Hi Jackson, In Qlikview, we can sort the table data only on Resident Loads only. Please follow below procedure.

               

              TEMP:

              LOAD *

              FROM SOURCETABLE.qvd (qvd) ;

               

              MAIN:

              Noconcatenate

              LOAD * Resident TEMP Order BY FIELDNAME