Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Display unique values from two tables in one list

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

Project1Anna5
Project2Erik10
Project3Stefan15

Table 2

ProjectName 

Projectmanager_ta

 

Days_ta

Project1Anna20
Project2Anna30
Project3Lars40

Outcome

Employees
Anna
Erik
Stefan
Lars

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

ProjectNameDays_prDays_ta
Project1520
Project230

Thanks in advance

1 Solution

Accepted Solutions
Not applicable

Re: Display unique values from two tables in one list

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;

3 Replies

Re: Display unique values from two tables in one list

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)


talk is cheap, supply exceeds demand
Not applicable

Re: Display unique values from two tables in one list

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.

Not applicable

Re: Display unique values from two tables in one list

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;

Community Browser