Adding missing values to a dimension table from fact table

[datagrrl]

I have a situation where my User Dimension is basically internal contacts, but external contact records are also in my fact table.
I want to add values from the fact table that are missing from the dimension table so that I will have a complete User Dimension. The Title for my external user would be something like 'Outside Sales'.
I included a Dummy App. I feel like this is easier than I am making it. I have tried a couple things with join and concatenate and I end up getting the wrong results.
Any assistance is appreciated.

[Qrishna]

unfortunately i cannot open your file.

did you already concatenate both the fields ?while appending, create another field called 'tab' and assign 1 and 2 for table1 and table2 . once appended, you could do a pick(match()) or an if() to get what you need on the UI.

[sbaro_bd]

Hi @datagrrl ,

Try something like this : 

Dimension:
LOAD
UserID,
Title
FROM DIMENSION_TABLE;

LOAD
UserID,
'Outside sales' as Title
FROM FACT_TABLE
WHERE NOT EXISTS(UserID)
;

 Regards.

[datagrrl]

Okay, the table 1 and table 2 think makes a little sense. 

How do I avoid duplicating the records from the dimension though when I load the records from the fact? Should I do some kind of join and add an exists clause? That is the part I am struggling with.

UserDim:
Load *,'Table1' as Tab
Resident User;

Concatenate(UserDim)
Load Distinct UserID,'Table2' as Tab, 'Outside Sales' as Title
Resident Fact;

Drop Table User

I end up with Duplicates of the values that existed in the User Dimension.

I really do not want to do any work in the UI. I just want to add the missing values to the user dimension. I already have a lot of data and don't want to do any more calculations in the UI.

I do think you might have lead me down the right path though and I can remove the duplicate records in another step. Again, I think I am making this more complicated than it needs to be.

[sbaro_bd]

@datagrrl , if you take a close look on my response, add a WHERE NOT EXISTS() statement on the second part of script.

Regards.

[datagrrl]

This doesn't make sense to me. 

Load Distinct UserID,'Outside Sales' as Title
Resident Fact
Where NOT EXISTS(UserID);

I do this and just end up with the same records that were in the User table in the first place.

[sbaro_bd]

The EXISTS() take a lookup on your previous charged tables! You're using the resident statements, that means all UserId records already exists on previous tables. Try something like this : 

UserDim:
Load *,
'Table1' as Tab,
UserId as KEY
Resident User;

Concatenate(UserDim)

Load Distinct 
UserID,'Table2' as Tab, 
UserId as KEY
'Outside Sales' as Title
Resident Fact
where not exists(KEY, UserId);

[Kushal_Chawda · Accepted solution]

@datagrrl  see the attached

[maxgro · Accepted solution]

User:
Load * Inline [
UserID, Title
ABC, Salesperson
DEF, Salesperson
BUR, Salesperson
JKL, Salesperson
FDR, Salesperson
QRS, Salesperson
TUV, Salesperson];

Fact:
Load * Inline [
UserID, Date, Value
ABC, 5/27/2022, 98
DEF, 4/27/2021, 41
BUR, 7/30/2022, 57
Thomas, 11/11/2020, 33
Jane, 2/18/2022, 48
ABC, 7/31/2021, 83
DEF, 6/26/2020, 74
BUR, 7/22/2020, 100
JKL, 12/4/2022, 100
FDR, 2/24/2021, 24
Palace, 10/6/2022, 92
QRS, 12/4/2020, 84
TUV, 8/29/2020, 69
Ranch, 6/21/2021, 1
Desert, 9/24/2022, 37
ABC, 7/16/2021, 98
DEF, 12/2/2022, 33
BUR, 9/10/2022, 14
JKL, 10/22/2021, 48
FDR, 3/4/2021, 39
QRS, 11/16/2021, 92
TUV, 8/3/2021, 48];

Tmp:
NOCONCATENATE Load Distinct UserID as NewUserID Resident User;

CONCATENATE (User)
load Distinct UserID, 'Outside sales' as Title
Resident Fact
where not exists(NewUserID, UserID);

drop table Tmp;

[datagrrl]

Thanks. I feel bad that you both posted the solution at the same time. I was definitely making it more complicated than it needed to be.