filter out the rows based on specific table key field

[surajap123]

Hi All,

I wanted to filter out the rows based on key field of a fact table that is peresent in different tab in qlikview script.

I have a table that has subset ratio of only 7%, so i want to remove those rows whose corresponding ids are not present in main fact table.

I thought of writing below script, but not sure how to point this exist condition to only fact table id. As this id field is present in many tables in data model which are linked to fact table.

Fact:

LOAD

id,

xyz;

...from fact;

--many tables in between these tables has id fields

SalesPerson:

LOAD id,

SalesPerson, from ...

Where Exists(id);

[maxgro]

maybe

tmp:

load distinct id as newid

resident Fact;

SalesPerson:

LOAD id,

SalesPerson, from ...

Where Exists(newid, id);

drop table tmp;

[surajap123]

Hi Massimo,

Thanks !!

However i am getting error incorrect syntact near ''newid''.

For more information. My table with Where Exists(newid, id); is pulling the data from source database.

[its_anandrjs]

Check the field name correctly may be the reason or you have to use Mapping table in place of the Where Exists.

[tresesco]

You can refer table in the Exists(), like:

Where Exists(TableName.id, id) ;

[maxgro]

remove comma after SalesPerson

and use a preceding load

tmp:

load distinct id as newid

resident Fact;

SalesPerson:

LOAD id,

SalesPerson

Where Exists(newid, id);

SQL SELECT *

FROM yourdatabasetable;

drop table tmp;

[maxgro]

this is a working example you can adapt to your req

OLEDB CONNECT32 TO ................;

A:

LOAD DEPTNO,

    DNAME,

    LOC;

SQL SELECT *

FROM TEST.DEPT

where DEPTNO <> 11;

Tmp:

load distinct DEPTNO as newfield Resident A;

B:

LOAD EMPNO,

    ENAME,

    DEPTNO

Where Exists (newfield, DEPTNO)   

    ;

SQL SELECT *

FROM TEST.EMP;

DROP table Tmp;

[surajap123]

Hi Massimo,

Thanks you so much for the script. It is working and i could fully reload the document.

However subset ratio is still 7% for the key field, in the table with 'where exist condition'. I am not sure why.

[maxgro · Accepted solution]

Subset ratio

• is the number of distinct values of this field found in this table as compared to the total number of distinct values of this field (that is other tables as well)

I suppose if the subset ratio in the table where you want to add the exists is 7% this means you have many different values in other tables not present in this table.

In your case

many Sales Person (id) values in fact tables

few Sales Person (id) values in SalesPerson

Check in your source db if this assumption is correct and let we know.

With exists you can limit the record loaded in Sales Person; subset ratio will not increase.

[surajap123]

Thanks you so much for clearing my basic doubt..