5 Replies Latest reply: Jun 5, 2013 9:00 PM by Will Wainwright RSS

    FirstSortedValue with string and date

      Hi guys,

       

      I have a table like the following:

       

       

      NameService NoDate
      abc105/2013
      xyz205/2013
      abc104/2013
      xyz204/2013
      abc103/2013
      xyz203/2013
      -102/2013
      xyz202/2013
      -101/2013
      -201/2013

       

       

      Now what i want is for any month to find the name accosiated with the service, or the most recent.

       

      E.g. service 1 on 03/2013, i require it to return xyz

       

      Which is easy.

       

      But if no month is selected i require it to return the most recent.

       

      e.g. Service 2 to return xyz

       

      I could use maxstring, but i need it ordered by date not alaphabetically.

       

      I've tried using firstsortedvalue(Name,Date) when it's filtered on a service but still no luck.

       

      Any ideas?

       

      Thanks so much in advance.

        • Re: FirstSortedValue with string and date
          Kumar Natarajan

          Hi,

           

          Try below mentioned script.

           

          Ex:

           

             

          LOAD ServiceNo,Date(Date#(Date,'MMM-YY'),'MMM YYYY') as Date,if(len(Name)=0,Peek(NewName),Name) as NewName;
          LOAD * INLINE [
              Name, ServiceNo, Date
              abc, 1, May-13
              xyz, 2, May-13
              abc, 1, Apr-13
              xyz, 2, Apr-13
              abc, 1, Mar-13
              xyz, 2, Mar-13
              , 1, Feb-13
              xyz, 2, Feb-13
              , 1, Jan-13
              , 2, Jan-13
          ]
          ;

           

          Find the attached file

          • Re: FirstSortedValue with string and date
            Stefan Wühl

            FirstSortedValue() should work just fine.

             

            You need a numeric Date, which I assume that's what you created (using QV date / time functions).

             

            Then create a table chart with service number as dimension and as expression

             

            =FirstSortedvalue(Name, -Date)

             

            [Minus sign is important to get the most recent / highest date, not the lowest]

            • Re: FirstSortedValue with string and date

              Finally fixed it.

               

              For anyone in future, there were two issues that I hadn't addressed.

               

              Firstly there were more than one name per Date, so a Distinct in the FirstSortedValue was needed.

               

              Secondly although i had a Date#(Date) in my initial load i didnt put what format the date was in.

               

              So my ending results were, in my load statement

               

              Date#(RIGHT([Date],7),'MM/YYYY') as [Date],

               

              this was to return the month and year only, I could have achived this with the date format at the top but i bring other dates into my model also.

               

               

              Secondly in the expression:

              =FirstSortedValue(DISTINCT mob_ServiceOwner,-[Date])

               

              Which returned the correct results, (although my qlikview doesn't recognise Distinct and puts the red line underneath to show error)