Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Join two tables based on condition in the second

I've got two tables Peopletable and Hourstable like this:

Peopletable

ProjectPersonProductRole
AAnnaCarManager
BAnnaTrainConsultant
AJohanCarConsultant
BJohanTrainConsultant

Hourstable

ProductManagerConsultant
Car13
Train47

I'm now constructing a report where you select a person and then see how many hours that person has accumulated per project. So that selecting "Anna" the output would be

Output

ProjectHours
A1
B7

I've got all the formulas working but I haven't found how to merge the tables. In the script I would like to do something like this, but that is not possible.

Peopletable:

LOAD

Project,

Person,

Product,

Role

FROM X;

Hourstable:

LOAD

Product,

Manager,

Consultant

FROM Y;

LEFT JOIN (Peopletable)

LOAD

Product AS Product

Manager AS Hours

RESIDENT Hourstable WHERE Role = 'Manager';

LEFT JOIN (Peopletable)

LOAD

Product AS Product

Consultant AS Hours

RESIDENT Hourstable WHERE Role = 'Consultant';

How do I correctly write this script? Thank you in advance.

1 Solution

Accepted Solutions
Not applicable

Re: Join two tables based on condition in the second

I would join the data like below.

Peopletable:

LOAD * Inline [

Project, Person, Product, Role

A, Anna, Car, Manager

B, Anna, Train, Consultant

A, Johan, Car, Consultant

B, Johan, Train, Consultant

];

Hourstable:

LOAD * Inline [

Product, Manager, Consultant

Car, 1, 3

Train, 4, 7

];

Temp:

LOAD Product, 'Manager'    as Role, Manager    as Hours Resident Hourstable;

LOAD Product, 'Consultant' as Role, Consultant as Hours Resident Hourstable;

left Join (Peopletable) LOAD * Resident Temp;

DROP Table Temp;

DROP Table Hourstable;

With this I will have a joined table like below

Untitled.png

Regards,

Sajeevan

8 Replies
Employee
Employee

Re: Join two tables based on condition in the second

I would use a Crosstable instead of the Joins....

Peopletable:

Load ... From X ;

Crosstable (Role, Hours, 1)

Load Product, Manager, Consultant From Y ;

You will get a synthetic key, but this is not a problem (as long as you don't have more tables.)

HIC

adiarnon
Contributor III

Re: Join two tables based on condition in the second

can you explain me the -

"WHERE Role = 'Consultant'"\ WHERE Role = 'Manager';

in the Hourstable there is no column like Role

i didn't get what you are trying to do....

Not applicable

Re: Join two tables based on condition in the second

Hi,

Yes, you could use Crosstable to solve your problem. If you still use JOIN then try as below:

TmpTable:

Load * from peopletable;

leftjoin

load * from hourstable;

Load *,

if(Role = 'Manager', Manager, Consultant) as Hours

resident TmpTable;

drop table TmpTable;

sujeetsingh
Honored Contributor III

Re: Join two tables based on condition in the second

Why are you limiting to scripts,they might make your model quite complex.

----Load all data from the source

----Append them to the required linking with keys and all that

----now try to produce the same in UI by different objects

thanks,

Not applicable

Re: Join two tables based on condition in the second

I would join the data like below.

Peopletable:

LOAD * Inline [

Project, Person, Product, Role

A, Anna, Car, Manager

B, Anna, Train, Consultant

A, Johan, Car, Consultant

B, Johan, Train, Consultant

];

Hourstable:

LOAD * Inline [

Product, Manager, Consultant

Car, 1, 3

Train, 4, 7

];

Temp:

LOAD Product, 'Manager'    as Role, Manager    as Hours Resident Hourstable;

LOAD Product, 'Consultant' as Role, Consultant as Hours Resident Hourstable;

left Join (Peopletable) LOAD * Resident Temp;

DROP Table Temp;

DROP Table Hourstable;

With this I will have a joined table like below

Untitled.png

Regards,

Sajeevan

Not applicable

Re: Join two tables based on condition in the second

You must convert your horizontal HourTable to a vertical versión:

HoursTableTmp: 
LOAD 
Product, 
Manager, 
Consultant 
FROM Y; 

HoursTable:
NoConcatenate LOAD
Product,
'Manager' AS Role,
Manager AS Hours
Resident
HoursTableTmp;
Concatenate LOAD
Product,
'Consultant' AS Role,
Consultant AS Hours
Resident
HoursTableTmp;

Drop Table HoursTableTmp;

Not applicable

Re: Join two tables based on condition in the second

Thanks for your suggestion, I went for this albeit a little modified since I had quite a few fields. It seems to work great and no confused data :-)

Not applicable

Re: Join two tables based on condition in the second

Thank you all for your replies and suggestions!

Community Browser