topic Re: Why does this script create a synthetic key? in QlikView

Why does this script create a synthetic key?

Tue, 22 Apr 2014 07:18:47 GMT

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?

Re: Why does this script create a synthetic key?

Tue, 22 Apr 2014 07:22:55 GMT

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..!

Re: Why does this script create a synthetic key?

ashfaq_haseeb — Tue, 22 Apr 2014 07:25:42 GMT

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

Re: Why does this script create a synthetic key?

vardhancse — Tue, 22 Apr 2014 07:27:01 GMT

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 .

Re: Why does this script create a synthetic key?

Tue, 22 Apr 2014 07:29:29 GMT

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!