topic Re: VLOOKUP nprinting not working in Qlik NPrinting

VLOOKUP nprinting not working

avastani — Tue, 12 Jan 2021 04:11:57 GMT

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

Re: VLOOKUP nprinting not working

Lech_Miszkiewicz — Tue, 12 Jan 2021 06:03:29 GMT

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-Column.htm

In short you have 2 options:

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

cheers

Lech

Re: VLOOKUP nprinting not working

Ruggero_Piccoli — Tue, 12 Jan 2021 11:13:41 GMT

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

Re: VLOOKUP nprinting not working

avastani — Tue, 12 Jan 2021 11:50:44 GMT

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

Re: VLOOKUP nprinting not working

Ruggero_Piccoli — Tue, 12 Jan 2021 12:01:39 GMT

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

Re: VLOOKUP nprinting not working

avastani — Tue, 12 Jan 2021 12:12:44 GMT

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?

Re: VLOOKUP nprinting not working

avastani — Tue, 12 Jan 2021 12:39:24 GMT

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,

Re: VLOOKUP nprinting not working

Ruggero_Piccoli — Tue, 12 Jan 2021 13:41:47 GMT

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

Re: VLOOKUP nprinting not working

avastani — Tue, 12 Jan 2021 13:48:44 GMT

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

Re: VLOOKUP nprinting not working

Ruggero_Piccoli — Tue, 12 Jan 2021 14:02:55 GMT

Hi,

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?
If the Sense table has not all data, update it and refresh the Qlik NPrinting connection cache
Add the sense table in the tables node of the Excel template
Drag and drop the columns in the Excel template
Add a delete row tag in the first white row after the table in Excel
Insert the native Excel table and include the row with the delete row tag
Insert a table side column with the Excel formulas you need
Run a preview

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

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

Re: VLOOKUP nprinting not working

Lech_Miszkiewicz — Tue, 12 Jan 2021 21:13:14 GMT

Long story short - vlookups and other excel based formulas work in NPrinting and both methods (level and excel named table) are good to populate all rows with it.

Now how to do it in your particular example? I don't know, as I dont have access to your environment, to your expressions, to your templates and actual formulas you have used.

Good starting point would be to post:

export of your template from NPrinting (there is export function in report section of admin console) (zip file)
result of your template output (excel file)
mockup of what you are trying to achieve... (also excel file)
screenshot of the properties panel

You must be making some trivial error there which prevents it from working...

I dont have an access to working Qlik Sense & NPrinting environment at the moment so I cannot record a video , but I may use NPrinting 16 and QlikView to show sample of steps which have to be done in NPrinting to make this working. Since it is excel functionality it will not really matter that I have used different version of NPrinting....

Re: VLOOKUP nprinting not working

Lech_Miszkiewicz — Tue, 12 Jan 2021 21:41:22 GMT

You know what - since this should take a lot less time and effort just DM me and we can jump on quick video chat/screenshare where I will be able to guide you through the steps required to make it working...

regards