Create Resident Table from Multiple tabes

[arpita]

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]

[jerem1234]

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]

CurrentMonth:

RiskIssueID	Issue Name	DueDate
A01094	Accounts leading to liquidity	15/12/2020
A20257	Lack of source data completeness	15/10/2020
A01065	Control Deficiency	15/02/2021
A20314	System inability to store a copy of Emails	15/04/2021
A20163	Incorrect margin call calculation	15/12/2020
A01006	potential risk of incorrect data	30/10/2020
A20314	System inability to sent to External Clients	15/04/2021
A20322	Incorrect Framework	15/11/2020

PreviousMonth:

RiskIssueID	Issue Name	DueDate
A01038	Known defects / deficiencies	15/08/2020
A20230	Gaps and weaknesses	15/12/2022
A20163	Incorrect margin call calculation	15/12/2020
A20229	Incorrect Rationale	30/06/2020
A01006	Potential risk of incorrect data	30/10/2020
A20257	Lack of source data completeness	15/10/2020
A01065	Control Deficiency	15/02/2021
A20194	Framework is not fully embedded	30/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]

@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