Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
Subset ratio
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.
maybe
tmp:
load distinct id as newid
resident Fact;
SalesPerson:
LOAD id,
SalesPerson, from ...
Where Exists(newid, id);
drop table tmp;
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.
Check the field name correctly may be the reason or you have to use Mapping table in place of the Where Exists.
You can refer table in the Exists(), like:
Where Exists(TableName.id, id) ;
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;
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;
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.
Subset ratio
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.
Thanks you so much for clearing my basic doubt..