Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Pass field references from text in Excel

Hi all,

I am currently importing text sentences saved in Excel to display in Qlikview. The idea is to make these sentences dynamic by referencing the current selections. So typically in Qlikview, you would create a text object and write:

=Field1 & ' has performed well this year with a growth rate of ' & Field2

However, my situation is more complicated and I would actually like to import the above as one value from an Excel cell. So based on my selections, I might have three different sentence options within a 'Sentence' field:

and also, ' &Field1& ' has performed well this year with a growth rate of ' &Field2

and also, ' &Field1& ' has performed poorly this year with a growth rate of ' & Field2

and also, ' &Field1& ' has performed very poorly this year with a growth rate of ' & Field2


So in the text object, I would just put


= Sentence

The problem is that when I put these sentences in Excel, Qlikview evaluates it all as a string, so '&Field1&' doesn't break out of the string to evaluate Field1.

Is there a trick to how I load in the data from Excel, or use variables, to get around this problem?

Thanks!

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

Try this in your text object. There is a space for the field & =vCAGR in your text column. One is like &=vCAGR and another is like & =vCAGR. If you can fix this in your excel sheet for Text column you can remove one of the replace functions in the below function.

=  Field1 &  ' did well this year, ' & Replace(Replace(Replace(Concat(Text, '|'), '&Field1&',Field1),'&=vCAGR',vCAGR), '& =vCAGR',vCAGR)

View solution in original post

9 Replies
vishsaggi
Champion III
Champion III

Can you upload a sample app and your excel sheet for a quick review?

Thanks,
V.

sunny_talwar

Can you share the exact code you are using here?

Not applicable
Author

Ok, added attachment. Please take a look!

vishsaggi
Champion III
Champion III

Try this in your text object. There is a space for the field & =vCAGR in your text column. One is like &=vCAGR and another is like & =vCAGR. If you can fix this in your excel sheet for Text column you can remove one of the replace functions in the below function.

=  Field1 &  ' did well this year, ' & Replace(Replace(Replace(Concat(Text, '|'), '&Field1&',Field1),'&=vCAGR',vCAGR), '& =vCAGR',vCAGR)

vishsaggi
Champion III
Champion III

To display text in the new line for each selection add this:

=  Field1 &  ' did well this year, ' & Replace(Replace(Replace(Concat(Text, '|' & Chr(13)), '&Field1&',Field1),'&=vCAGR',vCAGR), '& =vCAGR',vCAGR)

Not applicable
Author

Thank you Vish! Replace () worked very well. Once I simplified my terms, I was able to just use Replace(Replace(concat(Text), 'Field1',Field1),'vCAGR',vCAGR). Thank you!

Not applicable
Author

Correction: you don't even need concat() in the above

vishsaggi
Champion III
Champion III

WHere are you using that expression in the text object or in a table chart?? I used concat coz i used in the text object so just modify as necessary. I am glad it worked. Thanks for the update.

Not applicable
Author

Text object. Well I need concat in this sample.qvw, but in my real qvw it works without it!