Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Ethel
Creator III
Creator III

Keep vs join

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!
Labels (5)
1 Solution

Accepted Solutions
Bruddah_IZ
Partner - Contributor II
Partner - Contributor II

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

View solution in original post

2 Replies
Bruddah_IZ
Partner - Contributor II
Partner - Contributor II

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
Creator III
Creator III
Author

Thank you so much!

it worked!