3 Replies Latest reply: Nov 4, 2009 6:56 PM by Oleg Troyansky RSS

    Requesting help with writing an expression

      I am using in expression in a chart caption in order to display some additional data about the subject of my chart. My intention is to display the following within the caption:


      0000372/2 ANON E MOUSE, Science & Technology FRDSC

      Supervisory team: First Supervisor DELLA HAWKINS, Second Supervisor GERAINT MORGAN, Third Supervisor HELEN O TROY

      Registration date: 01/02/2008, Original Registration Expiry date: 31/02/2013, Current Registration Expiry date: 31/01/2012


      For which I have used the following expression (this may be rather clumsy, as this is my first QlikView application!):



      =(SCJCandNames &', ' &INC_NAME &'
      Supervisory team: ' &EXT_NAME &' ' &EEX_FNM1 &' '&EEX_SURN &', '&'
      Registration date: '&RDS_IDRD &',' &' Original Registration Expiry date: '&RDS_OERD &',' &' Current Registration Expiry date: '&RDS_CERD)


      However because each SCJCandNames (i.e. student) has more than one EXT_NAME &' ' &EEX_FNM1 &' '&EEX_SURN (i.e. supervisor), unless I make a(n inappropriate) selection on a single supervisor, the output does not display any of the supervisors' details, only the hard-coded text and punctuation marks.


      I presume that I need to incorporate some function into my expression which instructs it a) to display all instances of EXT_NAME &' ' &EEX_FNM1 &' '&EEX_SURN and b) the order in which to display them (perhaps? - I do already have the field relating to their unique sequence number specifically included as one of the sort criteria for the entire document).


      I've had a look through the forum messages, wikis, blogs and parts of the User Guide but haven't yet found anything obviously relevant to my query. Could someone please point me in the right direction with an explanation of what I need to do (and how, if you're feeling generous!). Many thanks.


        • Requesting help with writing an expression
          Peter Rieper

          Depends, what you want to achieve. You may use the MaxString()- or FirstSortedValue()-function or the like to display just one value for each field. Another idea might be to take the Concat()-function to show all possible values, separated by a definable delimiter - just making it appear like a text.


            • Requesting help with writing an expression

              Thanks Peter,

              I'm trying to include a list of all values of a combination of four fields from my denormalised data, loaded from a table file. So student AE Mouse would have a list of supervisors that comprised 3 groups of data wherein each group includes the Examiner Type, Forename and Surname fields for that instance. These will be ordered by the (undisplayed) sequence number of the group. Because I have the single data source I think this means Concat isn't applicable in this case (?).

              One of my colleagues with some Qv experience believes that my simplest solution would be to create a string of all supervisor type and name values (concatinating in the MS Excel sense,) prior to loading the data, rather than attempting to compile the list within the qlikview application. Does this sound reasonable in your experience?

              Many thanks