topic Re: Display unique values from two tables in one list in QlikView

Display unique values from two tables in one list

Fri, 07 Jun 2013 07:47:33 GMT

I've got the following data (ProjectName is key):

Table1:

ProjectName

Projectmanager_pr

Days_pr

Table2:

ProjectName

Projectmanager_ta

Days_ta

Now I wist to have a list showing all unique values from Projectmanager_pr & Projectmanager_ta. This so I can have a list of all the employees no matter if they are a Projectmanager_pr or Projectmanager_ta, then I can filter on their names and get their data no matter in what table they are listed.

Sample data:

Table 1

ProjectName	Projectmanager_pr	Days_pr
Project1	Anna	5
Project2	Erik	10
Project3	Stefan	15

Table 2

ProjectName	Projectmanager_ta	Days_ta
Project1	Anna	20
Project2	Anna	30
Project3	Lars	40

Outcome

Employees
Anna
Erik
Stefan
Lars

Selecting for example Anna above I would get the below results in another object:

ProjectName	Days_pr	Days_ta
Project1	5	20
Project2		30

Thanks in advance

Re: Display unique values from two tables in one list

Gysbert_Wassenaar — Fri, 07 Jun 2013 08:00:03 GMT

Concatenate the two tables in the script so you have only one table:

Table1:

load

ProjectName,

Projectmanager_pr as Projectmanager,

Days_pr as Days,

'Pr' as Type

from table1source;

concatenate(Table1)

load

ProjectName,

Projectmanager_ta as Projectmanager,

Days_ta as Days,

'Ta' as Type

from table2source;

Then in your table you can calculate the days for the different Types with expressions like this

Days_pr: sum({<Type={'Pr'}>} Days)

Days_ta: sum({<Type={'Ta'}>} Days)

Re: Display unique values from two tables in one list

Fri, 07 Jun 2013 09:17:03 GMT

Thank you both for your answers! This was a simplified example, in reality there are 50 fields per table.

Should I really join these big tables when I only want to join one field from each table?

Will my key stay the same still?

I just want a list of all the employees, they shouldn't be used as key.

Sorry, I am new to QV, hope I don't use the wrong terminology.

Re: Display unique values from two tables in one list

Wed, 12 Jun 2013 08:19:40 GMT

I managed to get it right by doing like this:

Table1:

LOAD

ProjectName AS ProjectName,

Projectmanager_pr AS Projectmanager_pr,

Days_pr AS Days_pr

FROM X;

Table2:

LOAD

ProjectName AS ProjectName,

Projectmanager_ta AS Projectmanager_ta,

Days_ta AS Days_ta

FROM X;

Combinedtable:

LOAD

ProjectName AS ProjectName,

Projectmanager_pr AS ProjectManager

RESIDENT Table1;

LOAD

ProjectName AS ProjectName

Projectmanager_ta AS ProjectManager

RESIDENT Table2;