Understanding subset ratio

[#1]

In my sample QVW file attached, I have the following tables -

Fact table - Main Data

Dimension - Carrier Groups

The key field is - [%Carrier Group ID]

I see that the key field in both tables have a subset ratio = 88%. I don't understand how that is possible. It should be 100%, right ?

Those are the only tables in the app.

Now, when you add the code below to my script, SR_Fact = 100%, but SR_Dim = 88%. Shouldn't both be 100% ? Also, the file size remains unchanged after this script was added. Why ?

//Temp table to reduce data

[Temp_Carrier_Groups_Dim]:

RIGHT KEEP ([Carrier Groups])

LOAD DISTINCT

[%Carrier Group ID]

RESIDENT [Main Data];

DROP TABLE Temp_Carrier_Groups_Dim;

[jagan]

Hi,

Try using Inner join

INNER JOIN  ([Main Data])

LOAD DISTINCT

[%Carrier Group ID]

RESIDENT [Carrier Groups];

Regards,

Jagan.

[#3]

Thanks for the point. But it does not answer my question.

[SergeyMak]

Hi

You have subset ration <100 because in Main Data you have %Carrier Group Id = "', or Null()

and you don't have appropriate value (Null() or "") in %Carrier Group Id in [Carrier Groups] table.

[#5]

Oh ! So subset ratio for FieldX is the number of distinct, NON-NULL and NON-BLANK values ???

[MK_QSL]

Information density of the field, which indicates the percentage of rows that contain a non-null value

Subset ratio, which shows the percentage of all distinct values for a field in the

table compared to all the distinct values for that field in the entire data model. It is

only relevant for key fields since they are present in multiple tables and do not all

share the same value.

Subset ratios can be used to easily spot problems in key field

associations.

     For example, when the combined total of subset ratios for multiple

tables is 100 percent, this may indicate that there are no matching keys between

these tables.

REF: QlikView 11 For Developers..

Let me give you simple example

Sales:

Load * Inline

[

  Customer, Sales

  A, 100

  B, 200

  D, 300

];

Customer:

Load * Inline

[

  Customer

  A

  B

  C

  D

];

If you write above sample script and will check the Table (CTRL + T), you will find two tables.

Sales and Customers

On Sales Table, if you hover the mouse on Customer field, you can see the Subset Ratio is 75% because there is not sales data for Customer C.

Now if you change the script for table Customer like below..

Customer:

Load * Inline

[

  Customer

  A

  B

  C

  D

]

Where Exists (Customer);

If will not load the Customer C as there are no sales data for the same.

Now check the Subset Ratio. It will be 100%.

If subset ratio is less than 100%, the key is called as Primary Key

but for 100% it is called as Perfect Key

[#7]

Hi Manish,

Thanks for above explanation.

But here my doubt is :

When Fact table showing 100% subset ratio and dimension table is showing only 88% subset ratio.

Does that mean Fact is containg more key records than Dimension..

Please advice.

[jagan]

Hi,

Yes the Fact table is have extra values when compared to Dimension table, then only this scenario occurs.

Regards,

Jagan.