topic Re: Filling Missing Data in QlikView

Filling Missing Data

MK_QSL — Thu, 25 Sep 2014 13:41:38 GMT

There is a good document on the subjected TOPIC by HC (Generating Missing Data In QlikView) but I want to have below requirements need to be achieved by Front End... Script side solution I already know...

Assume that I have below Data...

Sales:

Load *, Customer&Product as Key Inline

[

Customer, Product, Sales

A, P1, 100

A, P2, 120

A, P4, 200

B, P2, 140

B, P3, 210

B, P4, 165

];

I want below output...

Customer	Product	Total Sales
		935
A	P1	100
A	P2	120
A	P3	0
A	P4	200
B	P1	0
B	P2	140
B	P3	210
B	P4	165

Note that, only by unticking Suppress Zero Values on Presentation Tab is not going to give the expected result.

Here Customer A is not having Sales for Product P3 and Customer B is not having Sales for product P1.

In short, need to show All Products for All customers even though there is no Sales Data...

Re: Filling Missing Data

rubenmarin — Thu, 25 Sep 2014 13:53:26 GMT

Hi Manish,

Check attachment, maybe you can work on better performance but hope this helps.

Re: Filling Missing Data

Kwame — Wed, 03 Apr 2024 12:26:33 GMT

@MK_QSL @rubenmarin The attached document couldn't be accessed, could you please share the solution again.

Re: Filling Missing Data

rubenmarin — Wed, 03 Apr 2024 12:39:45 GMT

Hi, I can open the document, maybe you don't have a QlikView license.

The document only has this script:

Sales: Load *, Customer&Product as Key Inline [ Customer, Product, Sales A, P1, 100 A, P2, 120 A, P4, 200 B, P2, 140 B, P3, 210 B, P4, 165 ];

And a table with 2 dimensions:

=Aggr(Only({1} Customer), Customer)

=ValueList($(=Chr(39) & concat( Product, Chr(39) & ',' & chr(39)) & Chr(39)))

And one expression:

Sum(If(Product=ValueList($(=Chr(39) & concat( Product, Chr(39) & ',' & chr(39)) & Chr(39))), Sales))

Re: Filling Missing Data

Kwame — Wed, 03 Apr 2024 15:05:30 GMT

Much appreciated!