Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
Can you upload a sample app and your excel sheet for a quick review?
Thanks,
V.
Can you share the exact code you are using here?
Ok, added attachment. Please take a look!
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)
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)
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!
Correction: you don't even need concat() in the above
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.
Text object. Well I need concat in this sample.qvw, but in my real qvw it works without it!