7 Replies Latest reply: Sep 19, 2012 12:21 PM by whiteline _ RSS

    Tagging max number by Id

      Hi Everyone,

      I want to tag the ID with the max of number visit, but QV give me the error 'Invalid expression' .
      So, what i have tried is this:


      SCRIPT1

      Tab:

      LOAD Id,

           number_visit,

           max(number_visit) as Max_number_visit,

           if(number_visit < max(number_visit), 0, 'X') as Max_number_Tag

      from Tab1

       

      SCRIPT2

      Tab:

      LOAD Id,

           number_visit,

           max(number_visit) as Max_number_visit,

           if(max(number_visit),'X', 0) as No_visit_Tag

      from Tab1

       

      Both the above  scripts i have tried are not working for me!

       

      My Result should lool like this:

      ID
      number_visit
      No_visit_tag
      A10
      A20
      A3X
      B10
      B20
      B30
      B4X
      C10
      C2X

       

      PLEASE HELP!

        • Re: Tagging max number by Id
          whiteline _

          Hi.

          It's a bit strange that you're trying to use Max() aggregation function without 'group by' statement.

          Another thing that you use 'from' statement instead of 'resident'.

           

          What is your goal MaxNumber or Tag ?

           

          left join(Tab1)

          LOAD

               Id,

               max(number_visit) as Max_number_visit

          Resident Tab1

          Group by  Id;

           

          or

           

          left join(Tab1)

          LOAD

               Id,

               max(number_visit) as number_visit,

               'X' as No_visit_tag

          Resident Tab1

          Group by  Id;