Keep vs join

[Ethel]

Hello community,

I need your help to figure out what I'm doing wrong. I have HR fact in qvd with lots of keys, including PersonSKey  and PeriodSKey. I also have another qvd with information about leadership. Last year Person A could be manager, this year same person becomes Director of department and still can be Manager of division. Technically we still have 1 person, but this year he has 2 leadership roles.

If I'm doing 

FactHR:

Load 

     PeriodSKey 

    ,PersonSKey 

    ,HeadCount 

From [fact_HR.qvd] (qvd)

;

left join (FactHR)

LOAD 

PersonSKey ,

PeriodSKey ,

LeadershipRole     

FROM 

[HR_Leadership.qvd] (qvd);

I'm getting the wrong number of Headcount and no synthetic keys.

BUT If I'm doing 

FactHR:

Load 

     PeriodSKey 

    ,PersonSKey 

    ,HeadCount 

From [fact_HR.qvd] (qvd)

;

LEFT KEEP (FactHR)

LOAD 

PersonSKey ,

PeriodSKey ,

LeadershipRole     

FROM 

[HR_Leadership.qvd] (qvd); I'm getting the right headcount, but synthetic keys. 

My question  is it possible to get rid of synthetic keys and still have the right headcount? 

Thank you very much in advance!

[Bruddah_IZ · Accepted solution]

Hi Ethel,

Probably, the easiest solution would be just to create a composite key and use it to link FactHR with HR_Leadership table:

FactHR:

Load 

     PeriodSKey & '|' & PersonSKey As %PeriodPersonKey

    ,PeriodSKey

    ,PersonSKey 

    ,HeadCount 

From 

    [fact_HR.qvd](qvd)

;

HR_Leadership:

LEFT KEEP (FactHR)

LOAD 

     PeriodSKey & '|' & PersonSKey As %PeriodPersonKey

    ,LeadershipRole     

FROM 

    [HR_Leadership.qvd](qvd)

;

This way both your conditions should be satisfied.

Take care,

ilya

[Ethel]

Thank you so much!

it worked!