Why does this script create a synthetic key?

[#1]

I've got a script to load two different tables; linking those using ProductName and SDTQuarter.

My script looks like below (shortened). The SDT-table is loaded both from Sharepoint and from local saved file where the vQuarter-value has been stored.

LET vQuarter = Date(floor(today()), 'YYYY') & '-Q' & ceil(num(MONTH(Date(floor(today()),'M/D/YY')))/3,1);

SDT:
ows_Title AS ProductName,
'$(vQuarter)' AS SDTQuarter,
PMHours AS SDTPM
FROM Sharepoint 

tasks:
trim(subfield(ows_Product_x0020_Name, '#', 2)) AS ProductName,
Date(floor(ows_Task_x0020_Golive_x0020_Date), 'YYYY') & '-Q' & ceil(num(MONTH(Date(floor(ows_Task_x0020_Golive_x0020_Date))),'M/D/YY')/3,1) AS SDTQuarter,
subfield(ows_Project_x0020_Manager,'#',2) AS TaskPM
FROM Sharepoint

Running this script I get a synthetic table. If I remove line 11 all is fine.

I don't understand when why this is; I've tried to compare the SDTQuarter field as it looks in both tables and it is identical (eg. "2014-Q1") so QV should be able to match all.

Could anyone please help me understand what the problem might be?

[#2 · Accepted solution]

Hi,

u have 2 tables SDT and tasks.

If there are more than 1 key common in 2 tables, it will create synthetic key.

You have 2 keys common in ur tables:

1. ProductName

2. SDTQuarter

If not required rename one of the field name, this will remove ur synthetic keys.

Hope this will help u..!

[ashfaq_haseeb]

Hi,

If your both table are connected with more then 1 common field then synthetic key is created.

If you have same data you can comment in both field you can comment quarter from any one of the two.

If you want to create link on both the field from two tables, then create composite key instead.

hope it help.

Regards

ASHFAQ

[vardhancse]

Hi,

Any tables can be linked using one common field.

If there are more than 1 common field then it will re result to synthetic key.

Try to rename the SDTQuarter in the script level later in the front end(charts e.t.c..) u can rename .

[#5]

I see, I didn't know I could only have one key without synthetic. I'll just merge two fields and use that as key then. Thank you all for your help!