topic Re: Data on Qlik not showing the relations in QlikView

Data on Qlik not showing the relations

akpofureenughwu — Thu, 15 Jun 2017 10:03:06 GMT

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

Re: Data on Qlik not showing the relations

stabben23 — Thu, 15 Jun 2017 10:23:37 GMT

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

Re: Data on Qlik not showing the relations

akpofureenughwu — Thu, 15 Jun 2017 10:32:14 GMT

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

Re: Data on Qlik not showing the relations

stabben23 — Thu, 15 Jun 2017 11:08:47 GMT

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.

Re: Data on Qlik not showing the relations

stabben23 — Thu, 15 Jun 2017 11:10:47 GMT

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

Re: Data on Qlik not showing the relations

avinashelite — Thu, 15 Jun 2017 11:54:34 GMT

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

Re: Data on Qlik not showing the relations

akpofureenughwu — Thu, 15 Jun 2017 13:37:48 GMT

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

Re: Data on Qlik not showing the relations

akpofureenughwu — Thu, 15 Jun 2017 13:38:55 GMT

Here is a snapshot

Re: Data on Qlik not showing the relations

stabben23 — Thu, 15 Jun 2017 13:47:57 GMT

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

Re: Data on Qlik not showing the relations

stabben23 — Thu, 15 Jun 2017 13:49:56 GMT

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

Re: Data on Qlik not showing the relations

akpofureenughwu — Thu, 15 Jun 2017 14:28:07 GMT

Thank Staffan.. the Apply map function will meet the need of my script. I'm trying to apply it but I have a problem. Can you help me debug my script?

Here is it :

MapvTitleNametoiTitleId:

MappingvTitleNametoiTitleId from NPDCBI.crd.Title;

[Title]:

LOAD iTitleId,

vTitleName;

SQL SELECT iTitleId,

vTitleName

FROM xyz.crd.Title;

LOAD *;

ApplyMap('MapvTitleNametoiTitleId',iTitleId,null())as Title;

SQL SELECT iCommunityId,

iLeadershipPosition,

iCommunityLeaderId,

vSurname,

vMiddleName,

vFirstName,

vPhoneNumber,

iTitleId,

vEmail,

vPassport

FROM xyz.crd.CommunityLeaders;

Attached is d the model view

Re: Data on Qlik not showing the relations

akpofureenughwu — Thu, 15 Jun 2017 14:29:16 GMT

I want to do a vlookup (applymap) from title table to community leader table

Re: Data on Qlik not showing the relations

akpofureenughwu — Thu, 15 Jun 2017 16:28:31 GMT

I have mamaged to fix the bug...

Below is the correct script:

TitleMap:

Mapping Load Distinct

iTitleId,

vTitleName;

SQL SELECT iTitleId,

vTitleName

FROM xyz.crd.Title;

[Community]:

NoConcatenate LOAD

iCommunityId,

iLeadershipPosition,

iCommunityLeaderId,

vSurname,

vMiddleName,

vFirstName,

vPhoneNumber,

iTitleId,

vEmail,

ApplyMap('TitleMap',iTitleId, Null()) as [LeaderTitle];

SQL SELECT iCommunityId,

iLeadershipPosition,

iCommunityLeaderId,

vSurname,

vMiddleName,

vFirstName,

vPhoneNumber,

iTitleId,

vEmail,

vPassport

FROM xyz.crd.CommunityLeaders;

This link will help for further study: Mapping as an Alternative to Joining

https://community.qlik.com/blogs/qlikviewdesignblog/2012/09/18/one-favorite-function-applymap

Thank you Staffan

Re: Data on Qlik not showing the relations

akpofureenughwu — Thu, 15 Jun 2017 16:29:07 GMT

Thank you sir for your reponse