topic Re: Pivot Table in NPrinting 16 in Qlik NPrinting

Pivot Table in NPrinting 16

renuka_sasikumar — Wed, 25 Sep 2019 18:40:54 GMT

Hi,

I have a Pivot table in QlikView and want to export same in N-Printing.

How do I go about it, because when exporting Pivot Table from QlikView to N-Printing it's getting converted into Straight Table.

I want the below exact table in N-Printing Excel Report Distribution.

Group	Branch
Group1	ABXC
	XYZ
	Abhs
	hzxvch
	bhzb

Can anyone pls help me.

Regards,

Renuka S

Re: Pivot Table in NPrinting 16

arvind_patil — Fri, 20 Jul 2018 07:25:35 GMT

Hi Renuka,

1. Firstly You can take it as Image like same you designed in qlik.

2.Design pivot table using excel sheet option.

-> Firstly create straight table in qlikview/ Qliksense

-> Then go to nprinting designer

-> Add table into your excel designer sheet,

-> Seelect table range Menu Insert-> Pivot table-> Draw table as wish you.

Thanks,

Arvind Patil

Re: Pivot Table in NPrinting 16

Lech_Miszkiewicz — Fri, 20 Jul 2018 07:40:39 GMT

This was discussed many times on community.. Below are examples of different solutions. Pick and choose which one suits you best.

On the other hand i am surprised what you are saying that pivot table gets converted. I tested it and it does not...check here:Nprint Pivot table formatting issue

nprinting Excel with Pivot

How to Create a Pivot Table Report with QlikView Data

how to show pivot table in nprinting excel report

https://help.qlik.com/en-US/nprinting/June2018/Content/ExcelReports/Pivot-table-Excel.htm

Re: Pivot Table in NPrinting 16

renuka_sasikumar — Fri, 20 Jul 2018 08:39:26 GMT

Hi,

Thank You.

I already checked with the mention link. When a Pivot Table is placed in N-Printing & once you preview it, then it doesn't has the same Grouping which is seen in QlikView. see below it give the output has

Group	Branch
Group1	ABXC
Group1	XYZ

And I am not able to create Pivot using this link How to Create a Pivot Table Report with QlikView Data because my expression has Set Analysis used. So simply I can't put SUM(Sales) instead I have some conditions in it.

Regards,

Renuka S

Re: Pivot Table in NPrinting 16

Lech_Miszkiewicz — Fri, 20 Jul 2018 09:14:10 GMT

I disagree

you can still do this (even with the set analysis)

Just convert your pivot table to straight table in Qlik
Then you dont need to do any aggregation - to be frank you can use in Excel "Max\' function as all grouping is already done in Qlik and you only need to create artifitial grouping for repeated dimension values.

regards

Lech

Re: Pivot Table in NPrinting 16

renuka_sasikumar — Mon, 23 Jul 2018 10:06:36 GMT

Hi,

I followed the same step, I am getting the grouping but my variables are not working.

Say for e.g. My headings of the table is vMaxMonth then it is not coming as Jul-2018, instead its giving vMaxMonth only.

I am not able to do any formatting in the created Pivot Table. Tried many ways and still trying on it.

Regards,

Renuka S

Re: Pivot Table in NPrinting 16

Lech_Miszkiewicz — Mon, 23 Jul 2018 10:22:00 GMT

‌it is because you are not evaluating variable

depending where you are putting it you need to prefix it with = sign

Re: Pivot Table in NPrinting 16

renuka_sasikumar — Mon, 23 Jul 2018 11:04:51 GMT

Thanks for your prompt reply.

The same variable when I insert in second sheet, then I can see the value for it. But in the sheet where the Pivot Table is there it doesn't appear.

The same way when I insert the variable (<vDisb1> in the next sheet I am getting it.

Where vDisb1 is =='Q'&vMaxQtr &' Target per Month' . (heading for my table)

Regards,

Renuka S

Re: Pivot Table in NPrinting 16

renuka_sasikumar — Mon, 23 Jul 2018 12:21:30 GMT

Any reply on the above part.

Can anyone help.

Thanks in advance.

Renuka S

Re: Pivot Table in NPrinting 16

Lech_Miszkiewicz — Mon, 23 Jul 2018 12:54:49 GMT

‌i dont get it?

is this result in nprinting Or in Qlik?

my question is how is your column label looking like in Qlik table?

you might need to use $ sign expansion to get this right, but it all depends how you evaluate your variables.

To simplify provide us with screenahots of:

variable definition
label expression in qlik
result in qlik

Thanks

Lech

Re: Pivot Table in NPrinting 16

renuka_sasikumar — Tue, 24 Jul 2018 09:49:16 GMT

Hi Lech,

Thanks for your reply.

The result is in NPrinting.

Kindly find the below info.

vDisb1='Q'&vMaxQtr &' Target per Month'

Where vMaxQtr=MAX(IF([FIN YEAR]=vMaxYear,Fin_Qtr_No))

wherevMaxYear=MAX([FIN YEAR])

2.label expression in qlik.

='Q'&vMaxQtr &' Target per Month'

3. result in qlik.

Regards,

Renuka S

Re: Pivot Table in NPrinting 16

Lech_Miszkiewicz — Tue, 24 Jul 2018 10:47:56 GMT

i see

so like i said before you would have to use "=" sign in variable definition to evaluate it prior to bringin it to NPrinitng.

Open your app, go to Ctr+alt+V (or variable editor) and edit your variable by putting "=" sign in it so your variable would be:

vDisb1: ='Q'&vMaxQtr &' Target per Month'

If you do not want to re-factor your Qlik App you can create this label directly in NPrinitng by creating function in template where template formula would be simply:

='Q'&vMaxQtr &' Target per Month'

You could then use formula tag instead of column label coming directly from Qlik

more details here:

https://help.qlik.com/en-US/nprinting/June2018/Content/ExcelReports/Formulas.htm

cheers

Lech

Re: Pivot Table in NPrinting 16

renuka_sasikumar — Fri, 27 Jul 2018 06:21:36 GMT

Hi Lech,

I tried the same, but my data itself is not coming in Straight table. So I did some changes in QVW, I created that label as Variable. (vDisb1,vDisb2).

So in NPrinting I am taking as Chart Level. I can view the Data.

My output in the above template.

So after this when I do Insert-Table and the Design Summarize with Pivot Table, No data appears.

Can you help in this where I am missing if any point.

Thanks in Advance.

Re: Pivot Table in NPrinting 16

Lech_Miszkiewicz — Fri, 27 Jul 2018 07:00:25 GMT

few questions:

Why are you using level here? It will hit the performance a lot!!!

Second - why are you not using built-in excel Table?

did you try to follow steps i have provided isn some other posts?

nprinting Excel with Pivot

Re: Pivot Table in NPrinting 16

renuka_sasikumar — Fri, 27 Jul 2018 07:23:59 GMT

Level because as suggested (Reporting) and if I create Pivot table there is a chance of editing the output by un-hiding the sheet.

Second- Yes I tried the steps which mentioned in other post also with few more ways.

Output for the mentioned link-

But my main problem is the Dynamic label of the Report which is used as Variable.

Even if I do the same in Excel Pivot - same problem.

Regards,

Renuka

Re: Pivot Table in NPrinting 16

Lech_Miszkiewicz — Sat, 28 Jul 2018 00:36:16 GMT

I did little demo with screenshots as i do not know how else i can help you.

Note, If you will use dynamic column labels and would try to use Excel Pivot table your solution will not work as Microsoft Excel will not recognize new columns as those which will need to be added to Pivot View.

Below are steps on how to achieve dynamic labels in XLS template with NPrinitng.

cheers

Lech

My Data model in Qlik

My Front end Chart/Table

My variable definitions

My evaluation via $sign expansion

Building NPrinitng Template

OPTION 1 - Using NPrinting formulas!!! (i use formula to call variable created in Qlik. Here you can also directly put any formula which will return value (just like textbox in Qlik)

OPTION 2 - direct use of variables (depending on variables you use you might need to choose between option 1 or 2)

Preview - labels are working as expected (columns f & G)

Now Lets replace static column names with dynamic tags

Creating Excel Table

removing last row

Result as expected

Re: Pivot Table in NPrinting 16

renuka_sasikumar — Mon, 30 Jul 2018 04:51:44 GMT

Hi Lech,

Thank You for the demo in simple way.

Hope you got my query, if you see in my last and last second reply I have mentioned that my Dynamic variable is working till Straight Table but after when I convert into Pivot then there is a problem, i.e.:- Design Summarize with Pivot Table after all the step as mentioned by you.

Regards,

Renuka S

Re: Pivot Table in NPrinting 16

Lech_Miszkiewicz — Mon, 30 Jul 2018 05:01:33 GMT

so as mentioned before:

Note, If you will use dynamic column labels and would try to use Excel Pivot table your solution will not work as Microsoft Excel will not recognize new columns as those which will need to be added to Pivot View.

there is no solution for this in Excel and it is not NPrinitng limitation but MsExcel limitation!

Re: Pivot Table in NPrinting 16

Lech_Miszkiewicz — Mon, 30 Jul 2018 05:16:10 GMT

To add to my previous comment.

Before designing report in NPrinitng i would suggest to take into account following restrictions:

When using Excel Pivot table as a final solution make sure that all fields used in your PIVOT Table structure will have static name.
If you require dynamic names for some of the colums it maybe required to put them as values in a Qlik source table
If the format of the output is similar maybe you could use straight table with dynamic columns to achieve the same what you are trying to achieve with Pivot table
I would try to verify which parts of report are dynamic and affect report structure and which are just values flowing through as data changes with the time

if you could provide us with screenshots and steps you are trying to do to build your report maybe we could help you more. Your description though has to be very detailed including data model in qlik, screenshot of expressions and layout of object in qlik and then detailed screenshot of template.

If you dont want to use your own data maybe build a small Qlik app with dummy data, attach it here and provide an excel file with expected output, so we can start picking our brains on how to help you.

Your problem is a solution problem really and needs to be looked at with a particular data sample.

Re: Pivot Table in NPrinting 16

renuka_sasikumar — Mon, 30 Jul 2018 05:26:02 GMT

Can you try this same for doing it in i.e.:- Design Summarize with Pivot Table.

Hope you get the result and we can check what difference in our steps.

Regards,

Renuka S