Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
I want to do a vlookup (applymap) from title table to community leader table
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
Thank you sir for your reponse