Need help with hyperlink in qlikview tables

[#1]

Hi,

I am trying to put hyperlink for some change request number (CR ) in qlikview table. I am using the following expression (Also made the required changes in the display option on the chart properties):

concat(Replace(upper([ Issues]),'CR',''))&'<URL>'& 'http://ABC.com/Services/CR/CRDetail.cfm?CR_Number='&

concat(Replace(upper([Issues]),'CR',''))

Here i am also using the replace statement to remove the string CR as my "Issues" having values like CR12345, CR45678 etc but my link is ultimately using only the number like 12345, 45678 from the issues.

This works fine when each cell has only 1 value but the problem starts when cells have multiple values. in that case it starts showing hyperlink as

http://ABC.com/Services/CR/CRDetail.cfm?CR_Number= 12345, 45678

which is not correct. it should give me http://ABC.com/Services/CR/CRDetail.cfm?CR_Number= 12345

and http://ABC.com/Services/CR/CRDetail.cfm?CR_Number= 45678 separately.

Can someone please help with it?

Thanks in advance

[#2]

Firstly i would do the CR removal in the load script and alias as a separte field e.g.

Replace(upper([Issues]),'CR','') as [IssueNoCR],

This does two things, makes your expressions easier to debug and also moves the processing time of the string function to the reload instead of the render process.

now i am assuming both CR numbers are stored in the same field in the format "CR12345,CR45678" in which case you can use the function subfield on it e.g.

subfield(Replace(upper([Issues]),'CR',''),',',1) as [IssueNoCR_1],
subfield(Replace(upper([Issues]),'CR',''),',',2) as [IssueNoCR_2],

Now you can rewrite your link to be

IssuesNoCR_1&'<URL>'& 'http://ABC.com/Services/CR/CRDetail.cfm?CR_Number='&IssuesNOCR_1

or

IssuesNoCR_2&'<URL>'& 'http://ABC.com/Services/CR/CRDetail.cfm?CR_Number='&IssuesNOCR_2

as required.

Any more than two or three CR's however and you maybe looking at intergating a for next loop to dynamically subfield the Cr's and then transactionalise the dataset.