I need allways to show all products... how to do that in QV?

[#1]

Hi,

I have:

1 dim table "Products" with fields: '1' as #Products, %ProductID and Products (50 lines).

1 dim table "Clients" with '1' as #Clients, %ClientID and ClientCategory.
1 fact table "Sales" with %ProductID, Turnover and %ClientID. Not all products have sales.

In a straight table i selected dimensions Products, Client and ClientCategory
and created expressions Sum(Turnover), Sum(#Products), Sum(#Clients). 
Now when i select a client i get the Turnover  of sold products by category.

I need to show all the (50) products, even if there was no sales to any clients.

Allready tried to select "all values" in settings for dimension, but this does not work out.

Alse tried to LEFT KEEP LOAD SALES, but this does not work out eighter.

Can anyone help me with this? I really don't understand what i am doing wrong...

Rob.

.

[Anonymous]

Ron

On the Straight Table Presentation tab make sure the Suppress Missing and Suppress Zero Values are not selected.

Best Regards,     Bill

[sreenivas]

PFA file it may helpyou.

[MK_QSL]

wherever there are no products sold to certain clients you need to create 0 sales in script for those clients.

if you share your sample apps, would help you to create the same.

[maxgro]

like this?

try in the  attachment

[#6]

@

Hi Massimo,

Thanks for your reaction. I am using QV Personal Edition at home and could not start your attached qvw file.

I forwarded the file to my work adres and opened it (remote), but got the same message.... I don't understand that. I thought that i could open all/other  files from the QV edition on my work.

I succeeded trying to make an exemple data set myself. I copied my scriptcode in attached file.

After loading this script i have the wanted (20) products and  (50) customers.
The created sales data shows more lines then expected: sales records with not wanted ProductID's  (>15)
Only that should be a realtime example because in realtime i have product with no sales records.

I tried to make a selection on a resident file or qvd, but got the same result.

I must be doing something wrong here, but don't know what...

Rob.

[maxgro]

With Personal Edition you can only open the files you create

I changed my script, here it is

Product:

load

  rowno() as %ProductID,

  'Product ' & rowno() as Product,

  '1' as #Products

autogenerate 20; // 20 products

Client:

load

  rowno() as %ClientID,

  'Client ' & rowno() as Client,

  mod(rowno(),3) & ' category' as ClientCategory,

  '1' as #Clients

autogenerate 50; // 50 customers

Sales:

load

  rand() * 100 as Turnover,

  floor(rand()*15+1) as %ProductID, // only sales of products 1..15

  floor(rand()*50+1) as %ClientID // customers 1..50, change to limit

autogenerate 5000;

the chart is very simple, just 3 dims (sum) and 3 expressions

In the belowe images you find the flag I check/uncheck in Dimension tab (first and second image) and in Presentation tab (third image)

[maxgro]

regarding your script, it seems works just some very little change,

// Generate some customers

Customer:

Load

RowNo() As CustomerID,

//'Customer ' & Num(RowNo(), '(HEX)000') As CustomerName

'Customer ' & num(RowNo(), '000') As CustomerName

AutoGenerate(50);

// Generate some products

Product:

load

*,

Round(CostPrice, 0.01) As UnitPrice;

Load

RecNo() As ProductID,

//'Product ' & Num(RecNo(), '(HEX)00') As ProductName,

'Product ' & num(RecNo(), '000') As ProductName,

Round(Rand() * 1000, 0.01) As CostPrice

AutoGenerate(20);

// Load a mapping of product price

Product_Price_Map:

Mapping Load

ProductID,

UnitPrice

//From Product.qvd (QVD);                    //  don't have the qvd

Resident Product;

// Generate the sales data

Sales:

//Load *,                                                       // no need, calculated below

//SalePrice * Quantity As Sales;

Load

*,

1 as Aantal,

ApplyMap('Product_Price_Map', ProductID, 0) As SalePrice,

ApplyMap('Product_Price_Map', ProductID, 0) * Quantity As Sales;

;

Load

RecNo() As SalesID,

Ceil(Rand() * 15) As ProductID,

Ceil(Rand() * 50) As CustomerID,

Ceil(Rand() * 100) As Quantity

AutoGenerate(10000);                              // it was 100 !

exit script;

[#9]

OK Massimo, thanks! Your example works fine. This communicates a lot better.
When i select a client in the created chart, is see all products and salesdata from selected client. Excelent!

So, now i tried the same case on my worksite in realtime.

The result is clearly not as expected:
- when a (client) location is selected,  the productnames are only shown when there is a match with a sales record.

I have made some screenpics. And a copy of my script as it is right now. . 

For information:

Subset ratio of %Key overeenkomsten in table Overeenkomsten = 100%

Subset ratio of %Key overeenkomsten in table BonusFileLines = 35%

Subset ratio of %Key Locatie_Connectie in table Locaties = 99,22%

Subset ratio of %Key Locatie_Connectie in table BonusFileLines = 49%

Rob.

Example Script:

[Overeenkomsten]:
LOAD *,
[%KeyOVK OVK]  as [%Key Overeenkomst]

FROM \\QV-01\qlikview\Data\4_Base_QVDs\Overeenkomsten.qvd (qvd);

///$tab Turnover

LOAD *,
[BFL Locatie Connectie]     as [%Key Locatie_Connectie],           
[%KeyOVK BFL]     as [%Key Overeenkomst]

FROM \\QV-01\qlikview\Data\4_Base_QVDs\BonusFileLines.qvd (qvd);

///$tab Clnt_loc

[Locaties]:
LOAD *,
%LOC_STI_ID2  as [%LocatieStichting],
LOC_DebiteurNr  as [%Key Locatie_Connectie]

FROM \\QV-01\qlikview\Data\4_Base_QVDs\Locaties.qvd (qvd);

[maxgro]

did you "play" with flags as the above images?

or perhaps you can post the qlik doc; see here if you don't want to show your real data

100 Second Video - How to Reduce and Scramble a QVW

(but remember to make a backup, before)

last resource is

Generating Missing Data In QlikView

if I'm not wrong it explain how to populate a sparsely field