topic Re: How to load data with a filter on related table in QlikView

How to load data with a filter on related table

Mon, 19 Jan 2015 09:40:01 GMT

Hello! I have the following problem. I want to load data from three excel tables:

BUT I don't want to load sales in Brazil. So, my result must be:

If I could use SQL, I would wright the following code:

SELECT * FROM Sales LEFT OUTER JOIN Managers ON Sales.[Manager ID]=Managers.[Manager ID] LEFT OUTER JOIN Regions ON Managers.[Region ID]=Regions.[Region ID] WHERE NOT Regiopns.Name LIKE 'Brazil';

What loading commands should I wright in QlikView loading script??? I have been looking for solution about 5 hours and I couldnt solve this problem. Help, please!

Re: How to load data with a filter on related table

alexandros17 — Mon, 19 Jan 2015 09:44:43 GMT

Load * FROM Sales;

LEFT JOIN

Load * From Managers;

Inner JOIN

Load * From Regions Where [Region ID] <> 2;

let me know

Re: How to load data with a filter on related table

Anonymous — Mon, 19 Jan 2015 09:45:35 GMT

Something like this should work:

Sales:

LOAD

Amount,

Manager_ID

From Sales;

Left join(Sales)

Load

Name as ManagerName,

Manager_ID,

Region_ID

From Managers

where not Region_ID=2;

Left join(Sales)

LOAD

Names as RegionName,

Region_ID

From Regions;

Re: How to load data with a filter on related table

Mon, 19 Jan 2015 10:10:20 GMT

I lose manager with ID=3 if I use this script

Re: How to load data with a filter on related table

alexandros17 — Mon, 19 Jan 2015 10:13:49 GMT

So use this script,

TEMP:

Load * FROM Sales;

LEFT JOIN

Load * From Managers;

LEFTJOIN

Load * From Regions ;

MyTable:

noconcatenate

Load * resident TEMP Where [Region ID] <> 2;

drop table TEMP;

let me know

Re: How to load data with a filter on related table

Mon, 19 Jan 2015 10:25:08 GMT

Unfortunately, for this script I have the following strange result:

Re: How to load data with a filter on related table

Mon, 19 Jan 2015 10:34:21 GMT

IT WORKS! Thank you very much, Alessandro!