topic Re: Rigid Structure in Pivot Table in QlikView

Rigid Structure in Pivot Table

socura — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I need to create a cost accounting report as a pivot table with a rigid structure. That means it has to have the same number of rows for every combination of clickable Dimensions, even if there's no value. In addition to that the cells with no values should show 0 (zero). Reason for that is the controllers want to use the downloaded report to process them e.g. in excel with macros.

I fixed this problem by creating zero-values for every combination of dimensions using an outer join, but that blows up data extremely, increases file size and reduces performance. The original fact table contains several millions of entries with a lot of dimensions...

Anyone with an idea?

Mario

Re: Rigid Structure in Pivot Table

socura — Wed, 23 Mar 2016 07:48:38 GMT

P.S.: I tried checking and unchecking the supress NullValues and show all boxes in the different chart tabs, but that didn't work properly.

Re: Rigid Structure in Pivot Table

socura — Tue, 14 Jun 2016 15:33:06 GMT

Is there nobody to help me..?

Re: Rigid Structure in Pivot Table

sunny_talwar — Tue, 14 Jun 2016 16:20:21 GMT

I removed the script where you added 0 value and it seems to be working for me. What exactly isn't working for you? Would you be able to show it via images?

Re: Rigid Structure in Pivot Table

socura — Wed, 15 Jun 2016 05:26:07 GMT

Hi Sunny,

thank you for your answer.

Sorry, i forgot something very important. I'd like to create a pivot table with a cyclic group of dimensions. I changed the app. Please watch it with and without the generic 0 values.

Greetings

Mario

Re: Rigid Structure in Pivot Table

socura — Wed, 15 Jun 2016 06:06:15 GMT

I decreased the amount of records just loading 0 values for the combination of dimensions and accounts where is not existing any value, but that is still too much...

Re: Rigid Structure in Pivot Table

sunny_talwar — Mon, 27 Jun 2016 05:49:08 GMT

So basically regardless of which dimension you are looking at (Dim1 or Dim2), you would want to see same number of columns?

Re: Rigid Structure in Pivot Table

socura — Mon, 27 Jun 2016 08:26:14 GMT

No, the columns should be shown just for the selected dimensions with values.

Re: Rigid Structure in Pivot Table

socura — Mon, 11 Jul 2016 17:00:19 GMT

‌no chance to solve my problem?