Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
socura
Contributor
Contributor

Rigid Structure in Pivot Table

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

8 Replies
socura
Contributor
Contributor
Author

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.

socura
Contributor
Contributor
Author

Is there nobody to help me..?

sunny_talwar

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?

socura
Contributor
Contributor
Author

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

socura
Contributor
Contributor
Author

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...

sunny_talwar

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

socura
Contributor
Contributor
Author

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

socura
Contributor
Contributor
Author

‌no chance to solve my problem?