joining tables

[#1]

Hi I get headcount data that is sent to me on a weekly bases I need to come up with the joiners and leavers by joining the old headcount data with the new one. Whats relevant to me from the data is the Employee ID and Location. how do I come up with the people that just joined our staff and the one's that left. THANKS

[sinanozdemir]

Hi,

Maybe use Concatenate instead of joins and also use Where Not Exists(Prev_employeeID, EmployeeID) statement so that you only concatenate the new employees from the weekly data. I am assuming that you already have a date or timestamp field for you to track when those employees joined or left.

Hope this helps.

Thanks

[#3]

Hi thanks for your comment..... I've tried this but I doesn't seem to work correctly:

//Leavers:
//LOAD distinct [HC "Standard ID"] as SID
//FROM
//
//(ooxml, embedded labels, table is [Current Headcount]);
//
//NoConcatenate
//
//Leavers2:
//LOAD distinct
//[HC "Standard ID"]
//FROM
//
//(ooxml, embedded labels, table is [Prev HC])
//where not Exists(SID, [HC "Standard ID"]) ;

[sinanozdemir]

Try like this:

Leavers:

LOAD distinct [HC "Standard ID"] as SID

FROM

(ooxml, embedded labels, table is [Current Headcount]);

Concatenate -- Or you can omit Concatenate clause.

Leavers2:

LOAD distinct

[HC "Standard ID"]

FROM

(ooxml, embedded labels, table is [Prev HC])

where not Exists(SID, [HC "Standard ID"]) ;

Hope this helps

[#5]

Thanks for getting back to me..... this gave me the same result that I had previously

[sinanozdemir]

Do you happen to have sample data?