topic Re: How To Apply Excel Function Across Multiple Field Values in Qlik NPrinting

How To Apply Excel Function Across Multiple Field Values

bdroesch — Tue, 28 Jan 2020 15:28:01 GMT

I am creating an excel Nprinting report where I am listing different Products and two messages for each Product Name. In the attached screenshot, the Product Name is in the field <Alias> (called in cell A9) and the two messages appear for each Alias value in columns B & C. I am interested in outputting a Hyperlink for each Product Name that takes you to an internal link that is different for each ProductName. To create this hyperlink, I use the excel function "=HYPERLINK(G9,H9)" starting in cell D9. The HYPERLINK function, takes the input of the full string for the actual link (cell G9) but shows a more user friendly value to users by referencing the cell H9. When I generate the report, this excel function is not apply for every Alias value just for the first one. Do excel functions not run for each row? I will have many Product Names that are outputted (in the example there are just two).

A workaround that I tried was creating a level for Alias inside the Min_SeverityRate and put row 9 within in the Alias loop. This worked successfully as that loop will only output one Alias value per iteration. But this will not work once I increase the amount of data I bring in (i.e. increase the number of Alias values).

Perhaps there is a way that I can use a variable to print the text for the HYPERLINK Excel function after the excel is produced by Nprint?

Thanks,

Brian

Re: How To Apply Excel Function Across Multiple Field Values

Lech_Miszkiewicz — Wed, 29 Jan 2020 00:52:55 GMT

is <Alias> coming from level or Table tag? Looking at your template does not tell us about the source of the actual data and how you are nesting levels to produce it.

how about if we start from the beginning and learn how to create excel formulas as all the steps are clearly explained on official help page.

https://help.qlik.com/en-US/nprinting/November2019/Content/NPrinting/ExcelReports/Make-Calculated-Column.htm

in fact yu have 2 options:

- use of Insert-->Table excel functionality

- use of the lowest granularity level (which i assume you want to avoid)

giving us more details around how it is built may help to understand how we can help you,

Re: How To Apply Excel Function Across Multiple Field Values

Lech_Miszkiewicz — Wed, 29 Jan 2020 01:05:50 GMT

just another few comments/questions...

* i suggest using separate columns for LEVEL tags as they will affect column width and behaviour of the value tags

* is there any reason why you have 3 rollup levels? wouldn't it be better to create a table object which would already contain those fields...

* on your screenshot cell H6 is not populated when previewing report, why?