topic Re: Filter table based on other Table in QlikView

Filter table based on other Table

Wed, 13 May 2015 18:12:48 GMT

Table1

ID	Date1	Date2
ID1	01/01/2014	02/02/2014
ID2	03/03/2014	04/04/2014

Table2

ID	Type	Activity	Date1
ID1	A	A	01/02/2015
ID1	B	A	01/03/2015
ID2	B	B	20/02/2015

TableConsolidated:

ID	Date	From
ID1	01/01/2014	Table1
ID1	02/02/2014	Table1
ID2	03/03/2014	Table1
ID2	04/04/2014	Table1
ID1	01/02/2015	Table2
ID1	01/03/2015	Table2
ID2	20/02/2015	Table2

Hi, i am working on a report like the example above, where i have two tables in different formats which consolidated in tableConsolidated. Now i want to filter the TableConsolidated based on the fields from Table2, using the fields Type, Activity or any other field from this table. I Assume that since i link the 2 tables based on the field ID i could do this, but when i try to filter the field 'Type' selecting 'A', the TableConsolidated shows the result below:

D	Date	From
ID1	01/01/2014	Table1
ID1	02/02/2014	Table1
ID1	01/02/2015	Table2
ID1	01/03/2015	Table2

What i expect was only the dates that has an "A" in the "'Type" field. What kind of link do i have to do? In my complete table i have more than 20 field from table2 that i wish to filter in TableConsolidated

Re: Filter table based on other Table

ramoncova06 — Wed, 13 May 2015 18:35:49 GMT

we would have to see the data in order to be able to provide more details as what might be the issue

Re: Filter table based on other Table

petter — Wed, 13 May 2015 18:41:28 GMT

Do you want to do this in a Load Script or in the running application?

Re: Filter table based on other Table

Wed, 13 May 2015 18:46:56 GMT

In the Load Scrip

Re: Filter table based on other Table

petter — Wed, 13 May 2015 19:01:10 GMT

How do you do your filter now in the load script? While the load script is running there is no links between any tables. It is when the load script is about to finish that QlikView goes through it's association procedure and establish the resulting data model for the running application and/or the finally saved and reloaded QVW-file.

Re: Filter table based on other Table

Wed, 13 May 2015 20:06:19 GMT

Hi Petter, Follow attached part of my report. Table SITES is my Table1, REWORK my Table2 and DATAS my TableConsolidated. I created a filter "ReworkActivity" which is my "Type" from Table2. And [%Key field] is my ID. In The Graph i have two activities that can be filtered in Rework Activity selecting SI or WR. As you can see, if you filter the tables, the graph shows the right result, but if you filter the field "ReworkActivitY", for example "SI", it does not shows only the results for SI in Table2, it shows the result for every ID ([%Key field]).

Obs. You will not be able to reload because it access an intranet SharePoint.

Re: Filter table based on other Table

Wed, 13 May 2015 20:07:59 GMT

Hi Ramon, I responded to Pettor below with my database. BR

Re: Filter table based on other Table

petter — Wed, 13 May 2015 20:47:49 GMT

Well if you want the graph to only show activities for ReworkActivity that happens for DATASTypeField = Table 2 without specifically selecting Table 2 in the Tables list box you could simply put in a Set Expression in your Sum() expression in the chart (graph):

If( WildMatch( DATASType , 'Production*' ) , Sum( { < DATASTypeField = { 'Table 2' } > } DATASCount ) )

Then this graph will always just show activities for this specific DATASTypeField...

Re: Filter table based on other Table

Wed, 13 May 2015 21:07:58 GMT

In fact i want to select for example "SI" in ReworkActivity from table2, and it filter in table consolidated only the values that has SI atribute. In this case, show only "0.1.8 SI Rework" and exclude "1.1.8 WR Rework" from the graph.

Re: Filter table based on other Table

petter — Wed, 13 May 2015 21:21:57 GMT

Then I would go for this:

Sum( {<DATASTypeField={'Table 2'},DATASType={'Production*'},StepAtividade={'*SI*'}>} DATASCount)

Sorry - I am not fully into what you are trying to achieve - but this expression will make the graph only show "0.1.8 SI Rework" ... is this want you want then?

Re: Filter table based on other Table

Wed, 13 May 2015 23:48:46 GMT

No, I know it is difficult to explain. My aim is that the ReworkActivity (or otherTable2 Column), be able to filter the results in TableConsolidated (Which is Table1 and Table2 Together). The SI was just an example to say if i select 'SI', TableConsolidated should not present 'WR' results. But i need to filter based on any field selected in this listbox.