topic joining tables in QlikView

joining tables

Tue, 12 Jul 2016 13:19:05 GMT

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

Re: joining tables

sinanozdemir — Tue, 12 Jul 2016 14:44:41 GMT

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

Re: joining tables

Tue, 12 Jul 2016 18:14:47 GMT

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"]) ;

Re: joining tables

sinanozdemir — Tue, 12 Jul 2016 18:34:38 GMT

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

Re: joining tables

Tue, 12 Jul 2016 20:21:01 GMT

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

Re: joining tables

sinanozdemir — Tue, 12 Jul 2016 20:50:10 GMT

Do you happen to have sample data?