How to show zero values in Pivot even if data is not present in DB.

[nirav_bhimani]

Dear QVExpert,

I have requirement where I have a table which contains Forecast Category & Direct reportees Name as a Dimension & One Expression which shows the Revenue. So if I select Manager name & month Year as a selection then I want to display the ppl under him & their Revenue, My problem is only when I select manager & month if there is no data associated with that direct reportees then it is showing null value in the dimension, so instead of that it should show all Dimension data with 0 values as below table.

Note: I want the following Output in Pivot Table.

Forecast Category	Direct Reportees Name	Jon	Tom
Close		0	0
Bestcase		0	0
Commit		0	0
Pipeline		0	0

Currently due to no data present its showing like this,

Forecast Category	Direct Reportees Name	-
-		0

Can you please guide me how can I achieve this.

Thanks & Regards,

Nirav Bhimani

[nirav_bhimani]

HI,

Through Dummy data is working using some logic, but when I implement same in my current application its not working due to data model & linking betn them. Fields are not coming from once single table.

Regards,

Nirav Bhimani

[chematos]

You need to add to the dummy records all the fields you are going to use as filters on your dashboards, in order to have complete data for all your key fileds, doing in that way, whatever you select will not supress lines and you will always have the pivot table complete.

Let me know if you need further explanation to use the dummy records.

Regards,

Jose

[nirav_bhimani]

HI Jose,

How can i achieve this in Qlikview Script.

Regards,

Nirav Bhimani

[tresesco]

Have a look here: populating-a-sparsely-populated-field

[chematos]

Hi Nirav,

To create your dummy records you could do a time structure like this, notice that this is just an example for 2 years of data:

for a = 1 to 12

  DummyRecord:

  LOAD

  ($(vCurrentYear)) * 1000 + $(a) as DateYYYYMMM

  AutoGenerate 1;

NEXT

for a = 1 to 12

  Concatenate (DummyRecord)

  LOAD

  ($(vCurrentYear)-1) * 1000 + $(a) as DateYYYYMMM

  AutoGenerate 1;

NEXT

Now, you shoul join all the fields you need to use in your dashboard, these are fields from your fact tables. Also you should add the key fields you use for joining fact tables with dimensions, I use value '0' to join the dummy records, so:

Join

Load Field1,

     Field2,

     Field3,

     '0' as Field_Dimension1,

     '0' as Field_Dimension2

resident Fact_Table1;

You could do more joins with different fact tables and dimensions. After this join, you should concatenate the new structure to you fact table/tables, so:

Concatenate(Fact_Table1)

Load * resident DummyRecord;

Is important to know that you need to build the structure with '0' values first at your dimension tables, so you should "duplicate" your dimension setting your key field to '0', that's how you assure that when a '0' value is active/selected/possible from your fact table, every record of your dimension will be active/selected/possible, and that will happen no matter the filters selected if these filters are included in your dummy records as the example above.

You could try this solution, is not easy to get use to this methodology but can be very useful.

I hope this helps.

Regards,

Jose

[#7]

Nirav,

I am running into same issue, been stuck in this issue for couple of days now and haven't found any solutions yet.

It seems that when we uncheck 'Suppress Null Value' option, QLIKVIEW creates a separate category/row/row group to place rows/records with null/0 values.

Please do let me know if you come across a solution/workaround.

Thank you,

Prachyut.

[chematos]

Did you see my comments? I have used that solution many times, the thing is to join null values for each key field in order to fill the non-existent data on the database (non-existent key fields) with dummy records, then you will be able to show all the dimensions you want, even there are no records on the database.

regards,

Jose

[#9]

Jose,

Yes, I did see your comment, i have jot it down to use it in future. However, at the moment i am working on a POC and had we known this issue,  we would have implemented the workaround in load script in the beginning. But we are very close to the demo for POC, so i was looking for some other workarounds in front-end part; for instance - any expressions i could create/use for now.

Thank you Jose for your detailed note.

Cheers.

[chematos]

I understand the situation, however, under my point of view, the issue is not in the front-end. I think you cannot show data if you don't load it in the script. Imagine you don't have data for one period, so it won't be displayed in the pivot table, now imagine you can ignore the period filter with a calculated dimension in order to get all the possible values for this dimension, then your expression won't get the correct figures. If you can't solve it with a calculated dimension, then your expression doesn't matter because the rows won't be displayed.

Hope you find a solution soon

Good luck!

[#11]

Totally agree.