VLOOKUP nprinting not working

[avastani]

Hi,

Below is my nPrinting VLOOKUP setup but after running the report, I only get the engine to evaluate the first record and not all records of the table. What am I missing in the setup?

My report has no levels.

 I also tried by creating an expression in the Table in the App and referencing it but no luck despite disabling Keep source formats. The output repeats the formula but does not evaluate. How does one get native excel functions to evaluate in nPrinting

 

[Lech_Miszkiewicz]

Hi,

this is basic NPrinitng functionality with excel and is working as designed in your case.  Solutions on how to implement excel formulas in the excel tables are described in official tutorials here: https://help.qlik.com/en-US/nprinting/November2020/Content/NPrinting/ExcelReports/Make-Calculated-Co...

In short you have 2 options:

• you can use named table
• or you can use level to build your excel table

cheers

Lech

[Ruggero_Piccoli]

Hi,

I agree with @Lech_Miszkiewicz and I would like to suggest to use native Excel tables instead of levels because levels slow down the report generation speed. Levels work but I suggest to avoid them when they are not needed to keep the best performances.

Best Regards,

Ruggero

[avastani]

What would the level for this be? I have special formatting and other items that I cannot lose which native sense is unable to handle

[Ruggero_Piccoli]

Hi,

Use a native Excel table and add vlookup or other formulas you need on it. Formulas will ba automatically copied by Qlik NPrinting on all table rows when generating the final reports. You can also use Excel formatting features and apply them to the table. 

Levels were the only way to replicate custom Excel formulas on each row of a table on old Excel versions where native Excel tables were not yet supported (Excel 2003 and older). Using levels to create an Excel table with custom formulas to calculate something that is not available in Qlik Sense works but it is slower than using native Excel tables so I suggest the second solution.

You could also add the information directly in the Qlik Sense object or in the Qlik Sense load script if possible. 

Best Regards,

Ruggero

[avastani]

That is exactly what I am doing by adding the expression for vlookup in the sense object hoping it executed. What would the level for this object be? I cannot add the same chart as the level as I want the output at the table scope and not repeated. Do I create a dummy table and just use that?

[avastani]

Hi

I also tried using the table as is from sense as well as native Excel table and the expression with vlookup still did not execute to provide results. Doing trivial SUM() AVG() functions works fine but vlookup does not. It seems this is a persistent issue in other discussions on this board as well. An example would be helpful if have one handy

What am I missing here? Would appreciate your response. Thanks,

[Ruggero_Piccoli]

Hi,

Based on what I can see from your screenshots you are using VLOOKUP, that is an Excel function, in a Qlik Sense table column. Am I right? In a Qlik Sense table you need to use only documente Qlik Sense functions or add the Excel functions directly in Excel cells without the Qlik NPrinting tag characters (< and >).

Before working in Qlik NPrinting be sure that in the source Qlik Sense app you can see all the correct data.

Then you have to define the native Excel table in the Qlik NPrinting template. Refer to the official help site at https://help.qlik.com/en-US/nprinting/November2020/Content/NPrinting/ExcelReports/Intro-Excel.htm for all the details about Excel templates development.

Best Regards,

Ruggero

[avastani]

Hi,

As you can see there’s all options that I have tried. The official documentation is insufficient to show how something like this can work. I have used excel natively, via the expression in the sense object as well as used the excel table entirely. All options have been explored but none work.

It seems NPrinting truly does not support or work with complex excel functions. I have used all the officially documented methods to no success. If you have a handy example of complex excel inline column would appreciate the pointer

[Ruggero_Piccoli]

Hi,

1. Do you see all the data you need in the Qlik Sense app? If yes, could you post a screenshot also of scrambled data so we understand how the source table is?
2. If the Sense table has not all data, update it and refresh the Qlik NPrinting connection cache
3. Add the sense table in the tables node of the Excel template
4. Drag and drop the columns in the Excel template
5. Add a delete row tag in the first white row after the table in Excel
6. Insert the native Excel table and include the row with the delete row tag
7. Insert a table side column with the Excel formulas you need
8. Run a preview

All previous steps are documented here https://help.qlik.com/en-US/nprinting/November2020/Content/NPrinting/ExcelReports/Make-Calculated-Co...

I suspect you are doing something wrong because this works (I already did in the past) but I'm not able to understand what so could you share more screenshots? In details I would like to see the source data and structure and the generated report.

Best Regards,

Ruggero