Can Someone Please Help...

[samvile18]

Hi everyone - I have the following script that I'm using to create a mapping table giving me Master_Id by maximum campaign date. What I'm trying to do is load all the logins from the login table where the Master_ID is within the mapping table. It must be something to do with a where clause but I can't seem to get it right. Can anyone out there please help?

//Creates the campaign date mapping table to link to logins

CampaignDateMap:

Mapping LOAD Distinct

    %Master_Id,

    Num(Max(CampaignDate)) as CampaignDate  

Resident CampaignContacts

Where Email_Open = 1

Group By [%Master_Id];

///////////////////////////////////////////

LoginsTemp:

LOAD *

FROM [lib://CUSTMKT VIS - QVDs/2.Transformed\T_ENG_LOGINS_MDM_CLICKSTREAM.qvd](qvd);

Thanks in advance

[JandreKillianRIC]

Hi @samvile18 

First question is if you remove the mapping prefix on the table do you get the desired result? 

If so, where are you doing the ApplyMap function? 

[rwunderlich]

Are you trying to filter the logins by date or id? Or some combination of both?

-Rob

[samvile18]

I'm trying to reduce the login table to only return the Master_Id's that are within the mapping table...I suppose I don't need to use it as a map, I could keep the table and right keep maybe??

[rwunderlich · Accepted solution]

Not sure where Max(CampaignDate) comes into play here. But if your objective is to load only the Master_Ids where Email_Open = 1 then I think this would do the trick.

Temp:
Load %Master_Id as Master_Id_Filter

Resident CampaignContacts

Where Email_Open = 1;

LoginsTemp:

LOAD *

FROM [lib://CUSTMKT VIS - QVDs/2.Transformed\T_ENG_LOGINS_MDM_CLICKSTREAM.qvd](qvd)

Where Exists (Master_Id_Filter, Master_Id);

Drop Table Temp;

-Rob

[samvile18]

I didn't explain that very well did I - it's because there are multiple campaigns within the Campaign table so I only want bring back the Id's that match the latest campaign to have been run.