Left join not working after dropping resident table

[D19PAL]

Hi Guys,

FactTemp:

NoConcatenate

Load

 id

FROM [*********.qvd] (qvd)  ;

 left join (FactTemp)

LOAD

            first__id as id,

    Date

Resident *********;

Drop Table **********;

Right this is driving me crazy, I’m left joining id = id with a minimum load of 1000, so the id’s do match in both tables and I can see the Date field that I need.

But when I drop the resident table, I can see the Date field but no data, what is going on?

[Or]

I'm guessing your issue here is the "Load first 1000 records" aspect. If the data isn't presorted, the first 1000 rows in each table may not have any matching IDs. Keep in mind that the first 1000 rows doesn't mean "The first 1000 rows that join", it means "The first 1000 rows in the table".

If that isn't the issue, I would double check that the IDs actually match, including the data type (sometimes one ID is numeric and the other is text, for example).

[D19PAL]

Tried both of those things, there are matches in the first 1000 rows and i've text both sides.  I get the joins, that's not the problem, the problem is I lose the joined data when I drop the resident table.

[Or]

What you're saying is, you get the correct data from FactTemp when you remove the Drop Table statement in this situation? In other words, if you use the below code (renamed Date and removed the drop), does Date2 have the correct values (indicating the join is working correctly)?

Load

id

FROM [*********.qvd] (qvd) ;

left join (FactTemp)

LOAD

first__id as id,

Date as Date2

Resident *********;

[D19PAL]

Yes I'm getting the correct Date, but once I drop the Resident table, the data vanishes.

[Or]

Once you drop the resident table, the data from Date2 in the above script disappears? It shouldn't change - it's not even in the resident table..?

[D19PAL]

Exactly.  But it is.

So the resident table I'm getting Date from joins, but once I drop the resident table, I lose the date date from my  FactTemp table,

[Or]

I'm not sure that answers my question. Did you try running the version of the code I posted (with Date2 rather than Date and without dropping the resident) and then checking if Date2 is correctly populated? There's no reason why Date2 should change at all when you drop a resident table that doesn't contain Date2.

[xudo]

I have had the same issue many times. still no good solution found I'm afraid. 

What I tried was renaming the fields from the resident table I'm loading (adding a prefix and changing it to the name I want in the left join). This does not work either.

I would call this a bug.