Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arpita
Contributor III
Contributor III

Create Resident Table from Multiple tabes

I have 2 tables. Need to Look up from Table 1 for New Cases (if same RiskIssueID is present in Table2) and Lookup from 2nd table for Closed cases if same RiskIssueID is present in Table1). How to do it. Please advice

 

Table 1:

LOAD RiskIssueID,
[Issue Name],
[Impact Rating]

From Table1

Table 2:

LOAD RiskIssueID,
[Issue Name],
[Impact Rating]

 

4 Replies
jerem1234
Specialist II
Specialist II

Hi Onlinearpi,

There might be a couple of different approaches depending on your data and your data size. You can maybe use a Mapping table:

Table1Mapping:
Mapping Load
RiskIssueID,
'Closed' As IssueStatus
Resident Table1;

Table2Mapping:
Mapping Load
RiskIssueID,
'New' As IssueStatus
Resident Table2;

[Table 1]:
Load
RiskIssueID,
[Issue Name],
[Impact Rating],
ApplyMap('Table2Mapping', RiskIssueID) as IssueStatus
Resident Table1;

[Table 2]:
Load
RiskIssueID,
[Issue Name],
[Impact Rating],
ApplyMap('Table1Mapping', RiskIssueID) as IssueStatus
Resident Table2;

You can maybe use Left Joins like:

[Table 1]:
Load
RiskIssueID,
[Issue Name],
[Impact Rating]
Resident Table1;

[Table 2]:
Load
RiskIssueID,
[Issue Name],
[Impact Rating]
Resident Table2;

Left Join([Table 1])
Load
RiskIssueID,
'New' As IssueStatus
Resident Table2;

Left Join([Table 2])
Load
RiskIssueID,
'Closed' As IssueStatus
Resident Table1;

Or you maybe able to get the lookup function to work if you create a field for the status of the issue with:

Lookup('IssueStatusTable2', 'RiskIssueID', RiskIssueID, 'Table2') As IssueStatus1

Lookup('IssueStatusTable1', 'RiskIssueID', RiskIssueID, 'Table1') As IssueStatus2

Hope this helps!

Kushal_Chawda

@arpita  can you share sample data with expected output?

arpita
Contributor III
Contributor III
Author

CurrentMonth:

RiskIssueIDIssue NameDueDate
A01094Accounts leading to liquidity15/12/2020
A20257Lack of source data completeness15/10/2020
A01065Control Deficiency15/02/2021
A20314System inability to store a copy of Emails15/04/2021
A20163Incorrect margin call calculation15/12/2020
A01006potential risk of incorrect data30/10/2020
A20314System inability to sent to External Clients15/04/2021
A20322Incorrect Framework15/11/2020

 

PreviousMonth:

RiskIssueIDIssue NameDueDate
A01038Known defects / deficiencies15/08/2020
A20230Gaps and weaknesses15/12/2022
A20163Incorrect margin call calculation15/12/2020
A20229Incorrect Rationale30/06/2020
A01006Potential risk of incorrect data30/10/2020
A20257Lack of source data completeness15/10/2020
A01065Control Deficiency15/02/2021
A20194Framework is not fully embedded30/09/2020

 

Here if we see in both the tables, there are few common issues and some mismatch. The issues in Current month but not in previous should result as New and ones in Previous month but not in Current should come as Closed

Brett_Bleess
Former Employee
Former Employee

@Kushal_Chawda Some sample data provided, apologies if the notifications are still not working in Community, if you get a chance to circle back and have a look, much appreciated.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.