Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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,
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.
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$];
?
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,
Hi Jayaseelan,
I have attached the script (.qvf) in.
Thank you for your help.
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.
Hi,
Thanks for sharing the qvf file. We need Award.xls file for testing process.
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
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.
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