Assist please

[QFanatic]

hi guys

so as I understand it, I cannot use a pivot as-is from a QlikView model in Nprinting.

So I create a straight table, and then format it as a pivot in Excel in Nprinting.

so my straight table in Nprinting looks something like this..(see Attachment)

to create a pivot I have to select three rows(I'm using Year as an example here)..The first line selected would contain the heading (Year), the second - the actual field (<Year>) and a third blank line below - that will contain the data.

problem is - once I create the pivot, I always get a blank value in every table ..see attachment

Can any one assist - how do I get rid of these blank values in my pivot?

Much appreciated

[Lech_Miszkiewicz]

How about using <deleterow> tag from Extras section?

[QFanatic]

Hello Lech

How and where would I use it?. So the first line in my raw data is heading,
the next line the placeholder for the data and the third row the one that
actually contains the data.

I am assuming you say I should use Delete row on the third line? Would that
not delete the line that actually contains the data?

What exactly does deleterow do?

Much appreciated

[Lech_Miszkiewicz]

1st line is your heading

2nd line is your placeholder and data

3rd line is not used for data population by NPrinitng as NPrintng pushes down 3rd line as its creating rows (as many rows as you have in your source table). You can test it by putting some random text in row 3 and see what happens when you preview your report --> check image at the bottom of this post...

Now in order for your pivot table to include all rows you had to include initial 3rd line which normally is... BLANK and which gets pushed down (in my example 3rd line is becoming line 5! Once data is populated pivot table does not need to include this empty line so you can delete it using <deleterow> tag.

This is one of the purposes of <deleterow> tag in excel template

[QFanatic]

Thank you Lech for taking the time to explain this to me.

So in my Nprinting Designer - do I highlight the WHOLE 3rd row and then select Deleterow?

[Lech_Miszkiewicz]

just drag&drop deleterow tag on your 3rd row. I usually put it in column A. No need to highlight anything....

[QFanatic]

Me gain Lech :)-

I've tested it now. Added a dummy entry to line 3 in the datasource, refreshed the data source in the pivot by selecting Pivottable Tools - Analyze - Change datasource and selecting the whole range again...just to be safe.

I previewed the report again, and the dummy entry was not  in the new Pivot - it had only been added to the data table that I was using as a source.

Furthermore...I selected line 3 in the datasource, and selected "Deleterow" from the panel on the left...refreshed - still had "blank values"..crazy

Then I physically typed in <deleterow> in the third line - and then it did remove the <blank> values - what a mission.!

So it seems that I need to do the following every time...

1. Bring in the source table from QV into excel (like you did in your first sheet as well).

2. Create the pivot and run once - and live with the blank line..

3. Amend the source table and add <deleterow> to the 3rd line.

4. Run again.

do you agree?

Much appreciated

[Lech_Miszkiewicz · Accepted solution]

I would just follow steps from documentation:

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

I did not have to run anything...

My steps were:

• Drag and drop my table headers and value placeholder to cellse A1: C2
• Drag and drop <deleterow> tag to cell A3
• select range A1:C3
• Insert PivotTable--> and construct it according to my needs
• Preview
• thats it

[QFanatic]

oh my word, I have been so stupid! I selected "Deleterow' from the pane and never dragged and dropped it.

Thank you for your patience and help. Much appreciated

[Ruggero_Piccoli]

Hi,

Please use a meaningful title when you open a conversation here. It will help other people to help you.

Thanks.

Best Regards,

Ruggero