Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jiasheng
Creator
Creator

Help in Synthetic Key

Hi experts,

I need help in synthetic key.

My objective: to create a master calendar so that I can visualise a 3 year trend chart base on fiscal year(1 April-31 March).

My data scrip:

[HTD_AWARD_DATA$]:

LOAD

[AWARDED_DATE1]       as [A Awarded Date],

.

.

.

.

From

FROM [.......xls]

//Sort by Year
Fact:
load *,year([A Awarded Date])as Year resident HTD_AWARD_DATA$;


drop table [HTD_AWARD_DATA$];

My master calendar script:

MinMax:
LOAD
Max([A Awarded Date]) as MaxDate,
Min([A Awarded Date]) as MinDate
RESIDENT Fact;

LET varMinDate = num(Peek('MinDate',0,'MinMax'));
LET varMaxDate = num(Peek('MaxDate',0,'MinMax'));
LET vToday = $(vMaxDate);


Datefield:
LOAD date($(varMinDate)+IterNo()-1) AS Datefield
AUTOGENERATE (1)
WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);


Set vFM = 4 ;                                             // First month of financial year

Calendar:


Load Dual(fYear-1 &'/'& fYear, fYear) as FYear,          // Dual financial year
     Dual(Month, fMonth)              as FMonth,         // Dual financial month
          *;
Load Year + If(Month>=$(vFM), 1, 0)  as fYear,           // Numeric financial year,
         Mod(Month-$(vFM), 12)+1      as fMonth,         // Numeric financial month
          *;
Load

          Datefield as [A Awarded Date],

          Year(Datefield)                  as Year,      // Your standard master calendar
         Month(Datefield)             as Month,

Datefield
Resident Datefield;
DROP Table Datefield;

Result: When I load the data, it show that

$Syn 1 = A Awarded Date+Year

How do I clear this synthetic key or the key should be there?

Regards,

Frederic

1 Solution

Accepted Solutions
jayaseelan
Creator III
Creator III

Hi Jia Sheng Loh,

             In Fact Table you have ACENCY1 Field. you don't need that field means you can delete your Fact Table.

Directly you can load the Award.xls and take min and max table.

MinMax:

LOAD

Max([A Awarded Date]) as MaxDate,

Min([A Awarded Date]) as MinDate

FROM [lib://Desktop/Award.xls]

(biff, embedded labels, table is HTD_AWARD_DATA$);

Thanks,

View solution in original post

17 Replies
laddu_927
Specialist
Specialist

You are already creating Year in you table from Awarded date and again calculating year in calender with the same name and same value. I think you need not create year in fact table as it is being calculated in the Calender. If your requirement says to have both, try using different name for year in either Fact table or Calender. That will avoid synthetic keys.

jiasheng
Creator
Creator
Author

Hi Shiva,

I could not understand what do you mean. Do you mean if I do not need both, I will have to delete

//Sort by Year
Fact:
load *,year([A Awarded Date])as Year resident HTD_AWARD_DATA$;


drop table [HTD_AWARD_DATA$];

then

MinMax:
LOAD

Max([A Awarded Date]) as MaxDate,

Min([A Awarded Date]) as MinDate

RESIDENT [HTD_AWARD_DATA$];

?

jayaseelan
Creator III
Creator III

Hi,

    I your script your using field name Year in two times obviously synthetic key will come. For more clarity you may refer following link https://www.analyticsvidhya.com/blog/2014/12/remove-synthetic-key-concatenation-link-table-qlikview/


Else please post your .qvf file we will clear the synthetic key.


Thanks,

 

jiasheng
Creator
Creator
Author

Hi Jayaseelan,

I have attached the script (.qvf) in.

Thank you for your help.

OmarBenSalem
Partner - Champion II
Partner - Champion II

Yes, you do not need to create year in your fact table since you're creating it in the master calendar (or vis versa).

If you still want to have both years fields, please rename one of them.

jayaseelan
Creator III
Creator III

Hi,

   Thanks for sharing the qvf file. We need Award.xls file for testing process.

rahulpawarb
Specialist III
Specialist III

Hello Jia,

Hope that you are doing well!

By analyzing the script provided by you, I have few queries. Your inputs on this will help us to close this issue.

Why is the purpose of below given statements? What is the use of Year field though we have Year from MasterCalendar?

//Sort by Year

Fact:

load *,year([A Awarded Date])as Year resident HTD_AWARD_DATA$;

If you drop/remove the Year field from above load statement then you can get rid of Synthetic key.

Trust that this will be helpful.

Regards!

Rahul

Not applicable

Hi Jia,

The fields A Awarded Date and Year are appearing in both of your tables (Fact table and Calendar table). So that the synthetic key has been created.


Solution: Create a key by combining both the fields.



Regards,

Rajesh R. S.

jiasheng
Creator
Creator
Author

Hi Rahul,

actually I am not very sure about that table, it was a help from the community to create a me calendar. So if I do not need the script, I shall delete it?

Then where shall I resident MaxDate and MinDate to? Afterall I would need to MaxDate and MinDate to continue the script right?

LOAD
Max([A Awarded Date]) as MaxDate,

Min([A Awarded Date]) as MinDate

RESIDENT Fact;

Regards,

Jia