topic Reading value using Getpivotdata in Qlik NPrinting

Reading value using Getpivotdata

QFanatic — Tue, 17 Mar 2020 20:40:38 GMT

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

Re: Reading value using Getpivotdata

Ruggero_Piccoli — Wed, 18 Dec 2019 09:17:58 GMT

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-Nprinting/m-p/1607511#M25279

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

Re: Reading value using Getpivotdata

QFanatic — Wed, 18 Dec 2019 11:59:30 GMT

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

Re: Reading value using Getpivotdata

Lech_Miszkiewicz — Thu, 19 Dec 2019 05:55:34 GMT

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.

Re: Reading value using Getpivotdata

QFanatic — Thu, 19 Dec 2019 09:03:12 GMT

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

Re: Reading value using Getpivotdata

Lech_Miszkiewicz — Thu, 19 Dec 2019 09:37:08 GMT

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?

Re: Reading value using Getpivotdata

QFanatic — Thu, 19 Dec 2019 13:31:34 GMT

Hi Lech
Sent you a private message

Re: Reading value using Getpivotdata

Ruggero_Piccoli — Thu, 19 Dec 2019 15:43:34 GMT

Hi,

If you find a sharable solution, please share.

Best REgards,

Ruggero