topic Re: How to limit multiple conditions in a multi-table query? in QlikView

How to limit multiple conditions in a multi-table query?

Sun, 27 Apr 2014 08:04:36 GMT

Hi there,

Suppose have 3 tables:

Case table includes the following fields: case id, open date, close date, owner workgroup, closer workgroup, and so on.

Owner table include the following fields: owner workgroup, owner login name, and so on.

Closer table include the following fields: closer workgroup, closer login name, and so on.

I want to limit following conditions: open date between 2014/01/21 and 2014/03/21; owner workgroup is A; closer workgroup is B.

How to limit multiple conditions in a multi-table query?

Thank you in advance!

Re: How to limit multiple conditions in a multi-table query?

sundarakumar — Sun, 27 Apr 2014 08:44:26 GMT

Hi Bing,

I think u should first join all the tables so that all the fields that need to be filtered is in one single table. Then its easy to apply those filters.

Qlikview will not let u filter data referring to other tables that have been loaded before(in the scripting part).

If u want to keep them in different tables, u can find a key column that will link all the three table and u can filter out data while showing it in the Front end with Calculated dimension and Set analysis. But this is going to reduce the performance and the calculations would become complex and heavy.

So i think its best to do it in data modeling.

Hope this helps.

-Sundar

Re: How to limit multiple conditions in a multi-table query?

maxgro — Sun, 27 Apr 2014 09:05:28 GMT

start with

load the 3 tables (don't join)

use same names for the fields (qlikview will associate the tables in the data model)

- owner workgroup

- closer workgroup

add some chart

add 3 listbox to filter data (owner workgroup, closer workgroup, open date)

filter the charts using the listbox

- in open date listbox (example) >=21/01/2014<=21/03/2014

- in owner and closer just click on A and B

Re: How to limit multiple conditions in a multi-table query?

sundarakumar — Sun, 27 Apr 2014 09:08:48 GMT

I think he needs to filter data with query.

Re: How to limit multiple conditions in a multi-table query?

maxgro — Sun, 27 Apr 2014 09:40:00 GMT

if you want to (I prefere this way) stay with 3 tables

- load case table first; as case table has all the filter fields is possible to filter using a

where

[owner workgroup] = 'A'

and [closer workgroup] = 'B'

and [open date] >= ............... and [open date] <= ..........

;

- then load the other two tables; you can filter using a where exists, with a where [owner workgroup] = 'A'

if you want to join the tables in the script

- join first

- do a resident load of the result of previous join and filter (where ....)

Re: How to limit multiple conditions in a multi-table query?

sundarakumar — Sun, 27 Apr 2014 10:14:35 GMT

If u r going to have data as u have posted, then it is best to filter the data at script level by using all the filters in the same table. As u have all the Fields in the same table. Then while loading the rest 2 tables u can use where exists to filter out the data. If u r about to filter data with a field in other tables then u will have to join them.

say u have closegroup sales in closer table and u wan to filter data according to that also, u will have to join the tables to achieve it.

Hope this helps.

-Sundar

Re: How to limit multiple conditions in a multi-table query?

Mon, 28 Apr 2014 04:18:14 GMT

thanks for your advice.

i am sorry, i didn't explain the background.

actually, case table with not "owner workgroup" & "closer workgroup" field, need use "owner table id" & "closer table id" to joins the owner table and closer table.

for case table, there are more then 1 million cases every month; for owner table & closer table, there are more than 50 thousands account.

as results, in fact only very few cases, maybe 4 or 5 cases.

base on the background:

i can't filter case table with "owner workgroup","closer workgroup" and "open date", because there are not "owner workgroup" &"closer workgroup" field. if only filter "open date", for case table should be load more than hundreds of thousands cases. for only a few cases results, the amount of load data is too large.

So, i am looking for a better solution, more quickly and with less data.

Thanks again for your help!