Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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
Esteemed Contributor III

Re: Pass field references from text in Excel

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)

9 Replies
vishsaggi
Esteemed Contributor III

Re: Pass field references from text in Excel

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

Thanks,
V.

Re: Pass field references from text in Excel

Can you share the exact code you are using here?

Not applicable

Re: Pass field references from text in Excel

Ok, added attachment. Please take a look!

vishsaggi
Esteemed Contributor III

Re: Pass field references from text in Excel

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
Esteemed Contributor III

Re: Pass field references from text in Excel

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

Re: Pass field references from text in Excel

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

Re: Pass field references from text in Excel

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

vishsaggi
Esteemed Contributor III

Re: Pass field references from text in Excel

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

Re: Pass field references from text in Excel

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

Community Browser