topic Pass field references from text in Excel in QlikView

Pass field references from text in Excel

Thu, 04 Aug 2016 15:52:12 GMT

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!

Re: Pass field references from text in Excel

vishsaggi — Thu, 04 Aug 2016 16:26:34 GMT

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

Thanks,
V.

Re: Pass field references from text in Excel

sunny_talwar — Thu, 04 Aug 2016 16:40:27 GMT

Can you share the exact code you are using here?

Re: Pass field references from text in Excel

Thu, 04 Aug 2016 17:08:50 GMT

Ok, added attachment. Please take a look!

Re: Pass field references from text in Excel

vishsaggi — Thu, 04 Aug 2016 19:41:12 GMT

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)

Re: Pass field references from text in Excel

vishsaggi — Thu, 04 Aug 2016 19:45:17 GMT

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)

Re: Pass field references from text in Excel

Fri, 05 Aug 2016 13:32:15 GMT

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!

Re: Pass field references from text in Excel

Fri, 05 Aug 2016 15:04:45 GMT

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

Re: Pass field references from text in Excel

vishsaggi — Fri, 05 Aug 2016 15:23:20 GMT

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.

Re: Pass field references from text in Excel

Fri, 05 Aug 2016 15:41:47 GMT

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