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: 
ziabobaz
Creator III
Creator III

show "nothing" as Zeros in Pivot table

Hi!

I am totally confused.

Is there way to show missing values as Zero ('0')?

I know that these values are non-existent in this fields, but I am sure there should be a way of showin it as zeros.

Screenshot_5.jpg

I am using mastercalendar, if it matters.

This is my datamodel:

Screenshot_6.jpg

16 Replies
Anonymous
Not applicable

I think you'll have to create the missing entries manually:

Generating Missing Data In QlikView

If you created an inline example in English language, I could show you how to do,

but I neither have your sample files, nor am able to read russian (is it russian?)

ziabobaz
Creator III
Creator III
Author

Robin,

I have read the attached document and understand it from the general point of view, but when it comes to the code, i am stuck - just don't understand where i should start from. I am not a developer at all.

1. This is my test table. This script load before Master Calendar

TestTable:


LOAD *INLINE [

ДатаIx, Operation, Amount

2018-01-15, Sales, 500

2018-02-15, Sales, 1000

2018-03-15, Purchases, -400

];

2. There is also a Master Calendar script, created by my programmer, which is huge and i don't put it here. It goes after the first script above. The point is that this code generated a lot of dates in 2016-2018. As you understand, no data is present in all of these dates, except for the 3 dates above.

3. This is what i get:

Screenshot_7.jpg

The question is, do I need to fill missing 'Operations' for each generated date in order to get rid of this missing values? How?

As far as I understand, there should be a 3rd script, with something like:

Concatenate to Test Table

for each 'ДатаIx' from the Master Calendar , for each missing 'Operation' value, fill in this missing Operation value..

big_dreams
Creator III
Creator III

AFAIK, this is not possible in qliksense. It is possible in qlikview.

Anonymous
Not applicable

I prepared a demo for you...

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='YYYY-MM-DD';

SET FirstWeekDay=0;

SET BrokenWeeks=0;

SET ReferenceDay=4;

SET FirstMonthOfYear=1;

SET CollationLocale='de-DE';

SET CreateSearchIndexOnReload=1;

for i= MakeDate(2018,1,1) to MakeDate(2018,3,31)

    Master:

    LOAD Date($(i)) as Datefield AutoGenerate 1;

next

Facts:

LOAD *,Datefield&'|'&Operation as TEMPKEY;

LOAD *INLINE [

Datefield, Operation, Amount

2018-01-15, Sales, 500

2018-02-15, Sales, 1000

2018-03-15, Purchases, -400

];

//fill up Sales

concatenate(Facts)

LOAD

Datefield, 0 as Amount, 'Sales' as Operation  resident Master

where not Exists(TEMPKEY,Datefield&'|Sales');

//fill up Purchases

concatenate(Facts)

LOAD

Datefield, 0 as Amount, 'Purchases' as Operation  resident Master

where not Exists(TEMPKEY,Datefield&'|Purchases');

drop field TEMPKEY;

ziabobaz
Creator III
Creator III
Author

I am following you, thank you.

In real life I have >50 of Operation types. I wonder, do i have to replicate this for each Operation name.

I ended up by joining every possible Field combination to the Date field from the Calendar table (from this tread https://community.qlik.com/message/1489576?et=watches.email.thread#1489576)

It solved everything!

Thank you!

Anonymous
Not applicable

Just build all combinations like here in a separate table (no matching field)

Create a table made of two (for each)

If you need further assistance, please tell me that...

Mapuna
Partner - Contributor III
Partner - Contributor III

How to achieve this result from frontend using chart?