Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Synthetic Key Issues

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;

Capture.PNG

Any help would be greatly appreciated, thank you

Labels (4)
1 Solution

Accepted Solutions
Highlighted
Digital Support
Digital Support

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.

View solution in original post

2 Replies
Highlighted
Partner
Partner

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;

Highlighted
Digital Support
Digital Support

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.

View solution in original post