topic Re: Synthetic Key Issues Driving Me Mad in App Development

Synthetic Key Issues Driving Me Mad

Evan0211 — Mon, 17 Mar 2025 14:10:32 GMT

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.

Re: Synthetic Key Issues Driving Me Mad

marcus_sommer — Mon, 17 Mar 2025 15:05:17 GMT

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.

Re: Synthetic Key Issues Driving Me Mad

rwunderlich — Mon, 17 Mar 2025 15:12:16 GMT

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

-Rob

Re: Synthetic Key Issues Driving Me Mad

Evan0211 — Mon, 17 Mar 2025 16:32:02 GMT

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

Re: Synthetic Key Issues Driving Me Mad

Evan0211 — Tue, 18 Mar 2025 19:24:30 GMT

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.