topic Re: How to show zero values in Pivot even if data is not present in DB. in QlikView

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

nirav_bhimani — Tue, 22 Jul 2014 10:23:37 GMT

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

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

senpradip007 — Tue, 22 Jul 2014 10:27:56 GMT

Uncheck the "Supress Zero Values".

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

Anonymous — Tue, 22 Jul 2014 10:40:05 GMT

In the Presentation Tab

Last we have the Options like

Null Symbol -

Missing Symbol -

Replace the '-'(Hypen) with Zero('0')

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

nirav_bhimani — Tue, 22 Jul 2014 10:54:27 GMT

HI,

My requirement is when I dont have any data for the Direct reportees User & Forecast category but still I need to show the output with 0 value like below.

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

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

senpradip007 — Tue, 22 Jul 2014 10:55:54 GMT

Could you please upload sample qvw?

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

VishalWaghole — Tue, 22 Jul 2014 12:07:02 GMT

Hi Nirav,

Please find attached image for your reference..

Go to presentation tab -> click on appropriate expression and apply Missing Symbol as 0.

Hope it will work for you.

-- Regards,

Vishal Waghole

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

nirav_bhimani — Tue, 22 Jul 2014 12:20:50 GMT

Dear All,

Right now I am getting the following output, Bcoz there is no data associated to reportees & Forecast Category, So when I cross check the Forecast category all the values coming in gray, So due to that qlikview is showing "-" i.e. Null. But my requirement is that even if values are not there then also I need to show the data as I posted in my above post.

Forecast Category	Direct Reportees Name	-
-		0

Regards,

Nirav Bhimani

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

VishalWaghole — Tue, 22 Jul 2014 12:21:39 GMT

Hi Nirav,

also need to uncheck "Suppress Zero Values".

-- Regards,

Vishal Waghole

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

nirav_bhimani — Tue, 22 Jul 2014 12:33:12 GMT

HI,

I already did that, what I am saying is when I dont have any data in the database related to that Forecast category & reportees under him then also I want to display all 4 category as Close, Commit, Pipeline & Bestcase with respect to their reportees name & Expression value should be 0.

Regards,

Nirav Bhimani

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

VishalWaghole — Tue, 22 Jul 2014 12:36:54 GMT

Hi Nirav,

If possible then please add some sample data and expected output.

i can try to accomplish with your dummy data.

-- Regards,

Vishal Waghole

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

nirav_bhimani — Tue, 22 Jul 2014 12:53:41 GMT

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

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

chematos — Tue, 22 Jul 2014 13:10:31 GMT

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

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

nirav_bhimani — Wed, 23 Jul 2014 05:35:58 GMT

HI Jose,

How can i achieve this in Qlikview Script.

Regards,

Nirav Bhimani

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

tresB — Wed, 23 Jul 2014 05:38:19 GMT

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

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

chematos — Wed, 23 Jul 2014 12:23:10 GMT

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;

for a = 1 to 12

Concatenate (DummyRecord)

LOAD

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

AutoGenerate 1;

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

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

Wed, 06 May 2015 15:49:14 GMT

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.

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

chematos — Wed, 06 May 2015 17:46:05 GMT

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

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

Wed, 06 May 2015 18:02:58 GMT

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.

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

chematos — Wed, 06 May 2015 18:26:57 GMT

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!

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

Wed, 06 May 2015 19:34:21 GMT

Totally agree.