topic Re: filter out the rows based on specific table key field in QlikView

filter out the rows based on specific table key field

surajap123 — Mon, 19 May 2014 12:31:25 GMT

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

Re: filter out the rows based on specific table key field

maxgro — Mon, 19 May 2014 12:34:23 GMT

maybe

tmp:

load distinct id as newid

resident Fact;

SalesPerson:

LOAD id,

SalesPerson, from ...

Where Exists(newid, id);

drop table tmp;

Re: filter out the rows based on specific table key field

surajap123 — Mon, 19 May 2014 12:55:35 GMT

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.

Re: filter out the rows based on specific table key field

its_anandrjs — Mon, 19 May 2014 13:03:30 GMT

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

Re: filter out the rows based on specific table key field

tresB — Mon, 19 May 2014 13:10:07 GMT

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

Where Exists(TableName.id, id) ;

Re: filter out the rows based on specific table key field

maxgro — Mon, 19 May 2014 13:10:29 GMT

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;

Re: filter out the rows based on specific table key field

maxgro — Mon, 19 May 2014 13:18:08 GMT

this is a working example you can adapt to your req

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

LOAD DEPTNO,

DNAME,

LOC;

SQL SELECT *

FROM TEST.DEPT

where DEPTNO <> 11;

Tmp:

load distinct DEPTNO as newfield Resident A;

LOAD EMPNO,

ENAME,

DEPTNO

Where Exists (newfield, DEPTNO)

;

SQL SELECT *

FROM TEST.EMP;

DROP table Tmp;

Re: filter out the rows based on specific table key field

surajap123 — Mon, 19 May 2014 14:02:55 GMT

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.

Re: filter out the rows based on specific table key field

maxgro — Mon, 19 May 2014 16:12:48 GMT

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.

Re: filter out the rows based on specific table key field

surajap123 — Tue, 20 May 2014 13:56:28 GMT

Thanks you so much for clearing my basic doubt..