Data on Qlik not showing the relations

[akpofureenughwu]

Hello

I have a database that holds a lot of tables with defined relationship..

I have connected to the database via OLEDB.  #connection successful

roharoha‌ bill.markham‌ theresaa‌

on the frontend, I have created the table to visual the data.

My challenge is I can link the data from different tables

here are sample tables as they are on the

Company Table

vOMLId	dAward Date	dExpiration Date	iOperator Id	vContact TypeId	Status
Company A	04/04/1980	03/04/2000	1	P	1
Company B	02/03/1990	01/03/2010	3	T	1
Company C	03/04/1990	02/04/2010	2	P	2
Company Z	DD/MM/YYYY	DD/MM/YYYY	3	P	1

Operator Table

iOperator Id	Description
1	Sony
2	Samsung
3	Nokia

Contract Table   

Contract Type	Description
P	Permanent
T	Temporary

Status Table

Contract Type	Description
1	Producing
2	Non-Producing

My Proposed Table on Qlik front end

vOMLId	dAward Date	dExpiration Date	iOperator Id	vContact TypeId	Status
Company A	04/04/1980	03/04/2000	Sony	Permanent	Producing
Company B	02/03/1990	01/03/2010	Nokia	Temporary	Producing
Company C	03/04/1990	02/04/2010	Samsung	Permanent	Non-Producing

See attached for snapshots

[stabben23]

Hi,

you should rename some of the Fields in Your script.

IOperator Id should have a link in Your model

Rename Contract Type in Contract Table to vContact TypeId

Rename Contract Type in Status Table to Status.

Now you have a Connected model, also rename Description in both tables to Contract_desc and Status_desc or simular. This is to avoid a autolink from qlikview

[akpofureenughwu]

StatusId	Description
1	Producing
2	Non-Producing

Contract Table   

VContractType Id	Description
P	Permanent
T	Temporary

@staffan Johansson... Thank you for spotting those mistakes.. This is actually contract type , statusId for the databases...

I don't understand your solution.. Please throw more light

[stabben23]

Ok, Then you should Rename Status in Company Table to StatusId.

Contract Table will be Connected by default if the key have the same filedname.

You want to show all description in Your table, thats fine, but you must rename all Description in all tables.

Like Description as Contract_desc in cintract table

Description as Status_desc in status table

The use these desc in Your front end table.

[stabben23]

Can you show me a pic of the datamodel from Table viewer

[avinashelite]

Share a sample file with test data will help you out in building the model

[akpofureenughwu]

Here is the drift.... I have rename the table headers to that can link well on the data model view.. (see attached)/

What I need to know is how I can make thesee table communiicate data in the frontend to give me the proposed table.

I am currently using the LEft Join Statement to merge the tables so I can pull data from the contract type table to the company table since they both have vContractTypeId field. (this will make two tables became one)

Is this a good practice? Is there a better solution?

Regards   

[akpofureenughwu]

Here is a snapshot

[stabben23 · Accepted solution]

The model looks good, left join maybe will work, but if I should have done this I should use Applymap.

Don't join - use Applymap instead

[stabben23]

Your expected output in front end should be no problem With this model, just use correct fieldname.