Synthetic Key Issues Driving Me Mad

[Evan0211]

I am using Qlik Sense with a VizLib Gantt Chart. I have my data loaded and have 11 custom milestones, each coming from their own tables.

I have been tasked with custom coloring the milestones based on a pre-defined criteria. However, when I go into milestone styling and use an expression, it is not working. So I have tried going into the data loader and adding a key to each table so I can check against the key in the Color by Expression but no matter what I try, I keep generating synthetic keys. I know WHY it is happening, but I can't figure out a way to get around it.

The chart works exactly as we want it to work, with the exception of the dynamic coloring. 

Example of my data load:

Milestone_Roadmap_Link_AP:
Load
"ID",
"Milestone Title" as "_milestoneRoadmap_Title_AP",
"Milestone_Status_Stage" as "_milestoneRoadmap_Milestone_Status_Stage_AP",
"Current_Milestone_Date" as "_curMilestoneDate_AP",
Autonumber(1 & '|' & "ID") as Milestone_Roadmap_Key
Resident "Milestones"
Where "Milestone Title" like ('AP');

Milestone_Roadmap_Link_R_and_D:
Load
"ID",
"Milestone Title" as "_milestoneRoadmap_Title_R_and_D",
"Milestone_Status_Stage" as "_milestoneRoadmap_Milestone_Status_Stage_R_and_D",
"Current_Milestone_Date" as "_curMilestoneDate_R_and_D",
Autonumber(2 & '|' & "ID") as Milestone_Roadmap_Key
Resident "Milestones"
Where "Milestone Title" like ('R&D');

This is throwing a synthetic key on ID + Milestone_Roadmap_Key.

I have tried creating a lookup table, but that still throws a synthetic key. I have to have the ID in the tables, that is non-negotiable, but I need to be able to assign some kind of ID to the individual milestone to check against in the color expression.

Any help would be appreciated.

[marcus_sommer]

The data-model is IMO not suitable. Both tables (and maybe also the others) contain the same data and belong therefore into the same table. With "Milestone Titel" exists already an information to differentiate between the different sub-sets of data.

[rwunderlich]

Have you considered Concatenating these tables instead of keeping them seperate?

-Rob

[Evan0211]

I have not tried that approach, I will give that a try this afternoon. Thanks, I should have thought of that. 

[Evan0211]

Concatenating the data also does not work but I think I have gotten around the synthetic key issue by using a UniqueKey, however I now have an issue where the milestone inside the gantt chart does not see the dimension I have created that holds the color when the gantt chart expands. It only sees it when the chart expands AND the milestone is then clicked on.