Filling Missing Data

[MK_QSL]

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

[rubenmarin · Accepted solution]

Hi Manish,

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

[Kwame]

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

[rubenmarin]

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

[Kwame]

Much appreciated!