Change Source Table after Left Join

[Mathias_Siebenbuerger]

Hey community,

I have a question about the left join function:

To give you an example: I have two tables about product information:

1st Table has the Product ID (PID) and its Launch Date.

2nd Table has the Google Analytics Metrics like Pageviews per PID and Date. SO basically: PID, Date, Pageviews.

Now i want to left join the Launch Date via the PID into the Metrics Table. The Launch Table might contain PIDs with a future Launch Date. In this case, I do not yet have Metrics. Thus, this PID will not be joined to the Metrics table. No, i discovered that this in to-be-launched PID is even deleted in the Launch-Table after the left join. 

Do you have an idea why this happens? And can you explain a way how to avoid this? I only want to join the launch date into the 2nd table in order to calculate how many day we're out from the launch (num(Date) - num([Launch Date])).

Thanks in advance!

[Mathias_Siebenbuerger]

Update:
I just played around with the load editor:

It seems that the problem does arise from the left join but from the field PID which is the key between those two tables. The launch table only keeps those PIDs that are in the Metrics table. Does anyone know how i can keep all PIDs in the Launch table without losing the key between the two tables?

[ogster1974]

Why don't you create a mapping table from Product

Mapping

LaunchDateMap:

Load

PID,

LaunchDate

Resident

Product;

Then apply map it.

Metrics:

Load

*,

(num(Date) - num([Product Launch Date])) as "Days from launch"

Load

*,

ApplyMap('LaunchDateMap',PID) as "Product Launch Date"

Resident MetricsTmp;

[Mathias_Siebenbuerger]

Thanks for your reply!
Haven't thought of a mapping.
But then again, I have the problem where the Table with the PID and Launch Date only has the same PIDs as the Metrics Table due to the key (and vice versa). Is there a way to keep the PID as key and not lose any PIDs? Or would an Outer Join be a solution?

[ogster1974 · Accepted solution]

outer join would work

[Mathias_Siebenbuerger]

That worked, thanks!