Unmapped IDs Tagging

[nickmarlborough]

Hi, 

I have the following data:

[Mappings]: *THIS TABLE SHOWS EVERY ID THAT HAS A TEAM MAPPING*

LOAD

[ID],

[TEAM],

from [MAPPING TABLE]

[Table1]
NoConcatenate
LOAD
"Official ID" as [People ID]
FROM [SHEET 1]

[ID has link]:
NoConcatenate
LOAD
[ID] as [Linked ID]
resident [Mappings];

Concatenate ([Mappings])

LOAD
[People ID] as [ID],
'No Team' as [TEAM],
'Unmapped ID' as [Responsibility]
resident [Table1]
where not exists ([Linked ID],[People ID]);

Drop table [ID has link];
Drop table [Table1];

LOAD *THIS LOAD PULLS EVERY ID IN REGARDLESS OF A MAPPING AND TIES MORE INFO LIKE COUNTRY AND ID CODE ASSOCIATING TO THE MAPPING TABLE WITH [ID]*

[Official ID] as [ID],

[ID CODE],

[COUNTRY]

FROM [TABLE 2]

THE QUESTION: every ID will be aligned to a unique [ID CODE] and multiple [ID] can belong to a [ID CODE]....i want to add another tagging similar to the above but for [ID CODE] to essentially see the [ID CODE] does not appear in the MAPPING TABLE....once the [ID] field is associated. 

i keep getting a synthetic key....if someone could help that would be great!

[Sayed_Mannan]

Hi,

to address the issue of synthetic keys and ensure that you can tag unmapped IDs and ID codes effectively, you can follow these steps:

1. To avoid synthetic keys, you can rename fields or use the QUALIFY statement.

2. You need to create separate table to identify unmapped ID codes and then concatenate this information back to your main table.

here is what you can try:

// Load Mappings table
[Mappings]:
LOAD
[ID],
[TEAM]
FROM [MAPPING TABLE];

// Load Table1
[Table1]:
NoConcatenate
LOAD
"Official ID" as [People ID]
FROM [SHEET 1];

// Create a table to check linked IDs
[ID has link]:
NoConcatenate
LOAD
[ID] as [Linked ID]
RESIDENT [Mappings];

// Concatenate unmapped IDs to Mappings table
CONCATENATE ([Mappings])
LOAD
[People ID] as [ID],
'No Team' as [TEAM],
'Unmapped ID' as [Responsibility]
RESIDENT [Table1]
WHERE NOT EXISTS ([Linked ID], [People ID]);

// Drop temporary tables
DROP TABLE [ID has link];
DROP TABLE [Table1];

// Load additional information from Table 2
[Table2]:
LOAD
[Official ID] as [ID],
[ID CODE],
[COUNTRY]
FROM [TABLE 2];

// Create a table to check linked ID codes
[ID Code has link]:
NoConcatenate
LOAD
[ID CODE] as [Linked ID Code]
RESIDENT [Mappings];

// Concatenate unmapped ID codes to Mappings table
CONCATENATE ([Mappings])
LOAD
DISTINCT [ID CODE] as [ID],
'No Team' as [TEAM],
'Unmapped ID Code' as [Responsibility]
RESIDENT [Table2]
WHERE NOT EXISTS ([Linked ID Code], [ID CODE]);

// Drop temporary table
DROP TABLE [ID Code has link];

I hope this helps.