Unable to use ApplyMap properly

[james_hanify]

Hi all,

We are trying to do a connection to pardot using Rest and have the data. We would like to remove duplicates from a many to one relationship and turn it into a 1 to 1.

i.e.

list_email_id	email_id
1234	1
1234	2
1235	3
1236	4

Would become:

list_email_id	email_id
1234	1
1235	3
1236	4

I am wondering whether it is because it is resident - twice?

[map_emails]:

mapping load distinct [list_email_id],

[email_id]

Resident RestConnectorMasterTable WHERE [list_email_id] <> null();

[map_emails_contents]: // this was to check the mapping was done correctly

load distinct [list_email_id] as [list_email_id_temp],

[email_id] as [email_id_temp]

Resident RestConnectorMasterTable WHERE [list_email_id] <> null();

[UniqueEmailTable]:

LOAD Distinct

[list_email_id] as ListEmailId_TEMP,

ApplyMap('[map_emails]',[list_email_id], null()) as EmailId_TEMP

RESIDENT RestConnectorMasterTable Where [list_email_id] <> null();

Instead all it does is show this

ListemailId_TEMP	EmailId_TEMP
784917751	-

So presumably it couldn't find it, but clearly there is loads and mapping load picks the first one it finds i believe?

Thank you.

[james_hanify · Accepted solution]

Seemingly this fixed it, i can't have square brackets around the table name? I don't even know what the brackets do other than allows spaces I presume?

map_emails:

mapping load distinct [list_email_id],

[email_id]

Resident RestConnectorMasterTable WHERE [list_email_id] <> null() and [email_id] <> null();

[UniqueEmailTable]:

LOAD Distinct

[list_email_id] as list_email_id_TEMP,

ApplyMap('map_emails',[list_email_id], null()) as email_id_TEMP

RESIDENT RestConnectorMasterTable Where [list_email_id] <> null();