How To Apply Excel Function Across Multiple Field Values

[bdroesch]

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

[Lech_Miszkiewicz]

Hi 

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-Co... 

in fact yu have 2 options:

- use of Insert-->Table excel functionality

or

- 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,

[Lech_Miszkiewicz]

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?