Skip to main content
Announcements
Defect acknowledgement with Nprinting Engine May 2022 SR2, please READ HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
QFanatic
Creator
Creator

Reading value using Getpivotdata

hi all

 

I am building an Excel report in Nprinting. I have a field that links to a field in my DB. The user is able to amend the value (see Pic) by clicking the dropdown in the spreadsheet. The field that gets populated is <PERIOD_4>

My problem is - how do I "get" that value that the user selected, using GetpivotData. Its much more difficult since we're not working with absolute values, but in essence, Variables (<field>)

1. Pic 1 is the actual template and Pic 2 is what it looks like once it's run

2. In my Excel Pivot - where I'd usually say "=GETPIVOTDATA(" Cases Reported (all)",$A$10,"PERIOD",$A$2)", I assume I'd use "=GETPIVOTDATA(" Cases Reported (all)",$A$10,"PERIOD","<PERIOD_4>")"..but it doesnt work. I get a "#Reference" issue.

has anyone ever used this in Nprinting with success?

Thanks

 

 

 

Labels (2)
7 Replies
Ruggero_Piccoli
Support
Support

Hi,

Excel GetPivotData() function works in Qlik NPrinting Excel templates. You can see an example here https://community.qlik.com/t5/Qlik-NPrinting-Discussions/Calculted-value-from-a-pivot-table-Nprintin...

I don't understand your sentence "I have a field that links to a field in my DB.". Qlik NPrinting uses data from QlikView or Qlik Sense, not from generic DB. So first you have to create apps or documents then connect them with Qlik NPrinting.

Can you share the screenshots of the shole template and the result? It is very hard to understand you scenario with partial images. Thanks

Best Regards,

Ruggero



Best Regards,
Ruggero
---------------------------------------------
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads with a LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads with LIKEs if you feel additional info is useful to others.
QFanatic
Creator
Creator
Author

Ruggero,

I'm attaching some samples of what  I want - silly data - but you will understand what I want.

The Capture.jpg...this is the view that the user wants to see. He wants to select the Period from the dropdown, and then the chart refreshes....Have a closer look at the Expressions - specifically where Period <> Flagmonth. I would need to

1) Create a pivot based on this straight table

2) In the pivot, use Period as Filterfield

3) create 2 new fields - both of them use the pivot as the source. The field Flagmonth in the GetPivotTable would need, for the <>, point at whatever Period was chosen in the dropdown, and here is where my problem comes in. Period is no longer a dimension in the table, and because both expressions now depend on the value in the dropdown, they dont work - as currently is.

 

I hope this makes my problem a bit clearer? In essence because the filter field is used in the way it is, and is NOT a dimension in the table, the field FlagMonth needs to 'look up' the value the user advised.

 

Hope that helps

 

 

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

how big is your data set and how many periods users will be able to choose from in your NPrinting report?

The idea I have is very simple by creating a linking table with many-to-many periods and flags include exclude in the table which would be used as a source for NPrinitng export. This obviously would explode the number of rows in your final export, as it would create releationships for each combinations of possible to filter from periods....  but on the other hand would make your NPrinting report easy to build. This is just high level idea

solution would look like this, but first you would have to create a cross-join and create flags in qlik.Capture.PNG

 

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
QFanatic
Creator
Creator
Author

Hello Lech

I have 24 000 records in my fact table, and a max of 12 periods that they can/should extract. Problem is, at this stage I have Period and Flagmonth field. Another one gets added after this - processfield. Not sure what you think about adding a third date field?

 

Thanks

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

Well. This is now down to your datamodel in QlikView. If your fact table has around 24000 records, creating a bridging/linking reference table for the purpose of nprinting does not change anything in your model (other than adds one extra table which has no impact on the rest)

The only thing it changes is the final Straight table chart on QlikView ui which will create roughly 288000 records in excel to produce required for Nprinting output. 


having a sample of your app would help me more - maybe you can shared scrambled version?

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
QFanatic
Creator
Creator
Author

Hi Lech
Sent you a private message
Ruggero_Piccoli
Support
Support

Hi,

If you find a sharable solution, please share.

Best REgards,

Ruggero



Best Regards,
Ruggero
---------------------------------------------
When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads with a LIKE if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads with LIKEs if you feel additional info is useful to others.