topic Requesting help with writing an expression in QlikView

Requesting help with writing an expression

Tue, 03 Nov 2009 18:56:36 GMT

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

prieper — Wed, 04 Nov 2009 02:27:52 GMT

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.

HTH
Peter

Requesting help with writing an expression

Wed, 04 Nov 2009 20:52:36 GMT

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

Sophie

Requesting help with writing an expression

Oleg_Troyansky — Wed, 04 Nov 2009 23:56:52 GMT

Sophie,

you don't really need to create the string upfront. You can use function concat() to create the list of AVAILABLE values on the fly, with a delimiter of your choice:

concat( F1 & ' ' & F2 & ' ' & F3, ',')

cheers,