Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
SimonDB
New 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

1 Solution

Accepted Solutions
Support
Support

Re: Synthetic Key Issues

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 don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.

View solution in original post

2 Replies
Partner
Partner

Re: Synthetic Key Issues

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;

Support
Support

Re: Synthetic Key Issues

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 don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.

View solution in original post