Synthetic Keys Issue

[Bharathi09]

Hi all,

I am having Synthetic keys issues in my script Year and KeyCol

KeyCol should be common Column between tables and Year is forming Synthetic key

I am using Year to create Activity Date column, I need to check standard cost for particular Activity date filter also along with other Filters

If I drop Year column, Standard cost is showing same for a particular Lane for all months

For example if I have lane 72J , Standard cost for Activity date 2023-01 is 9.11 and 2024-01 is 4.88, sum is 14.99
but when i click 2023-01 showing 14.99 and for 2024-01 also 4.88, it's not changing according to activity date

INPUT_DATA:

LOAD 

     Code

     [JIT Route] as Lane, 

     YEAR as Year, 

     MODE,

     'Standards' as "Record Type",

     REPLACE(LTRIM(REPLACE(Code, '0', ' ')), ' ', '0') as bill2,

     Right([JIT Route],Len([JIT Route])-Index([JIT Route],'-',1)) as destination,  

SubField([JIT Route], '-', 1) as origin,

     "Standard Cost", 

     [BASE RATE], 

     STD_F, 

     [AVG], 

     [TOTAL STANDARD],

     LPM_AUG, 

     LPW_AUG, 

     LPM_SEPT as LPM_SEP, 

     LPW_SEPT as LPW_SEP, 

     LPM_OCT, 

     LPW_OCT, 

     LPM_NOV, 

     LPW_NOV, 

     LPM_DEC, 

     LPW_DEC, 

     LPM_JAN, 

     LPW_JAN, 

     LPM_FEB, 

     LPW_FEB, 

     LPM_MAR, 

     LPW_MAR, 

     LPM_APR, 

     LPW_APR, 

     LPM_MAY, 

     LPW_MAY, 

     LPM_JUN, 

     LPW_JUN, 

     LPM_JUL, 

     LPW_JUL

FROM 2023.xlsx

//where (Right([JIT Route],Len([JIT Route])-Index([JIT Route],'-',1)) = '02459 - 02462')  and (SubField([JIT Route], '-', 1)='27458 J');

Olddata:

 load *,

origin & ' & ' & destination & ' & ' & Lane & ' & ' & "Record Type" & ' & ' & Year as KeyCol

Resident INPUT_DATA;

Drop table INPUT_DATA;

CrossTableLPM:

CrossTable(MonthLPM, STD_LPM, 2) 

Load

    origin & ' & ' & destination & ' & ' & Lane & ' & ' & "Record Type" & ' & ' & Year as KeyCol   ,

    Year,

    LPM_JAN as JAN, LPM_FEB AS FEB, LPM_MAR AS MAR, LPM_APR AS APR, LPM_MAY AS MAY, LPM_JUN AS JUN, 

    LPM_JUL AS JUL, LPM_AUG AS AUG, LPM_SEP AS SEP,

    LPM_OCT AS OCT, LPM_NOV AS NOV, LPM_DEC AS DEC

Resident Olddata;

CrossTableLPW:

CrossTable(MonthLPW, STD_LPW, 2) 

Load

   origin & ' & ' & destination & ' & ' & Lane & ' & ' & "Record Type" & ' & ' & Year as KeyCol,

   Year,

   LPW_JAN AS JAN, LPW_FEB AS FEB, LPW_MAR AS MAR, LPW_APR AS APR, LPW_MAY AS MAY, LPW_JUN AS JUN,

   LPW_JUL AS JUL, LPW_AUG AS AUG, LPW_SEP AS SEP,

   LPW_OCT AS OCT, LPW_NOV AS NOV, LPW_DEC AS DEC

Resident Olddata;

STD_COST:

LOAD *,

    Date(Date#(Year & Right(MonthLPM, 3), 'YYYYMMM'),'YYYY-MM') as "Activity Date"

    Resident CrossTableLPM;

Drop Tables CrossTableLPM;

Concatenate(STD_COST)

LOAD

*,

Date(Date#(Year & Right(MonthLPW, 3), 'YYYYMMM'),'YYYY-MM') as "Activity Date"

Resident CrossTableLPW;

DROP Fields  MonthLPW, MonthLPM;

Drop Table CrossTableLPW;

Exit Script;

Please try to resolve the issue
Thanks

[MartW · Accepted solution]

Hi @Bharathi09 ,

In Qlik a Synthetic Key forms when there are 2 or more field are linking to the same table.

please take a look at this support page:

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/synthetic-keys... 

+-----------------+       +---------------------+
|     Table 1     |       |      Table 2        |
+-----------------+       +---------------------+
| ID              |       | Order               |
| Field 1         |-------| Field 1             |
| Field 2         |-------| Field 2             |
+-----------------+       +---------------------+

the best thing to do is at the end of the script to drop one of the fields or to rename one of the fields that create a synthetic key.

+-----------------+       +---------------------+
|     Table 1     |       |      Table 2        |
+-----------------+       +---------------------+
| ID              |       | Order               |
| Field 1         |-------| Field 1             |
| Field 2         |       | Field 3             |
+-----------------+       +---------------------+