Synthetic Key Issues

[SimonDB]

Hi, I have an issue with Synthetic Keys and am not sure how to fix this, or does it need fixing?

I have 3 tables that I wish to join, but to make the joins work properly I get synthetic keys. I've created a small dummy data to represent what I have. I cant see a way for creating a key by joining fields and if I do a join in my full data set I end up with duplicated data.

// Load Location Data
UNQUALIFY *;
L_TMP:
LOAD * Inline [
NAME, LOCATION
Bill, Office1
Jill, Office2
Fred, Home
Mary, Office1
Unassigned];

QUALIFY *;
UNQUALIFY "%_*";
LOCATION:
LOAD *
,NAME as %_NAME
Resident L_TMP;

Drop Table L_TMP;

// Load Work Order Data
UNQUALIFY *;
W_TMP:
LOAD * Inline [
KEY, ASSIGNED, DESCRIPTION
WO1, Mary, Find Cat
WO2, Fred, Read Book
WO3, Mary, Build Shed
WO4, Jill, Mow Lawn
WO5, Fred, Make Breakfast
WO6, Unassigned, Cook Dinner
WO7, Fred, Clean House
WO8, Fred, Wash Clothes
];

QUALIFY *;
UNQUALIFY "%_*";
WORK_ORDER:
LOAD *
,ASSIGNED as %_NAME
,KEY as %_WO
Resident W_TMP;

Drop Table W_TMP;

// Load Task Data
UNQUALIFY *;
T_TMP:
LOAD * Inline [
KEY, NAME, DESCRIPTION, WORKORDER
A1, Bill, Look Inside, WO1
A2, Jill, Look Outside, WO1
A3, Mary, Get Mower, WO4
A7, Jill, Fuel Mower, WO4
A5, Mary, Cut Lawn, WO4
A9, Bill, Get Wash Powder, WO8
A6, Unassigned, Collect Clothes, WO8
A4, Bill, Clean Upstairs, WO7
A10, Unassigned, Clean Downstairs, WO7
A8, Jill, Dry Clothes, WO8
A11, Mary, Paint Doors, WO21
];

QUALIFY *;
UNQUALIFY "%_*";
TASKS:
LOAD *
,NAME as %_NAME
,WORKORDER as %_WO
Resident T_TMP;

Drop Table T_TMP;

Any help would be greatly appreciated, thank you

[fkeuroglian]

Hi how are you?

Question: You want to join? or only have a relation between tables?

If you want to join, you have to use the JOIN FUNCTION IN THE SCRIPT between tables.

If only you want to have a relation, try this code:

Drop Table L_TMP;

// Load Work Order Data
UNQUALIFY *;
W_TMP:
LOAD * Inline [
KEY, ASSIGNED, DESCRIPTION
WO1, Mary, Find Cat
WO2, Fred, Read Book
WO3, Mary, Build Shed
WO4, Jill, Mow Lawn
WO5, Fred, Make Breakfast
WO6, Unassigned, Cook Dinner
WO7, Fred, Clean House
WO8, Fred, Wash Clothes
];

QUALIFY *;
UNQUALIFY "%_*";
WORK_ORDER:
LOAD *
,ASSIGNED as %_NAME
,KEY as %_WO
Resident W_TMP;

Drop Table W_TMP;

// Load Task Data
UNQUALIFY *;
T_TMP:
LOAD * Inline [
KEY, NAME, DESCRIPTION, WORKORDER
A1, Bill, Look Inside, WO1
A2, Jill, Look Outside, WO1
A3, Mary, Get Mower, WO4
A7, Jill, Fuel Mower, WO4
A5, Mary, Cut Lawn, WO4
A9, Bill, Get Wash Powder, WO8
A6, Unassigned, Collect Clothes, WO8
A4, Bill, Clean Upstairs, WO7
A10, Unassigned, Clean Downstairs, WO7
A8, Jill, Dry Clothes, WO8
A11, Mary, Paint Doors, WO21
];

QUALIFY *;
UNQUALIFY "%_*";
TASKS:
LOAD *
,NAME as %_NAME
,WORKORDER as %_WO_TASK
Resident T_TMP;

Drop Table T_TMP;

[Brett_Bleess · Accepted solution]

Simon, have a look at the following Design Blog post, I think it will help you out here:

https://community.qlik.com/t5/Qlik-Design-Blog/Synthetic-Keys/ba-p/1472634

Regards,
Brett