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 Jayaseelan,
Due to data sensitivity, I have only able to provide ,[A Agency], [A Awarded Date] and [A fiscal Year], which I think is what you will need to solve the synthetic key.
I also attached a new .qvf to remove all the unnecessary script.
Hi,
1.I have Checked your Test Application, In that there is an Fact Table your are using Year field. You can get year field from master Calendar then you can use it in your application.
2.Compulsory you need Year Field in Fact Table then rename and use it in your application.
I have attached your updated test application.
Please find it.
Thanks
Hello Jia,
Generally, MasterCalendar will hold all kind of date related fields such as Year, Quarter, Month, Week, Day & DateKey etc. and Fact table will contain a DateKey and rest of measures/metrics. Using DateKey Qlik can join data from MasterCalendar and Fact table.
I believe, you can drop the Year field from Fact Table. You can make use of Min. Date and Max. Date using Resident table to define start and end date of MasterCalendar table.
Hope this will be helpful.
Regards!
Rahul
Hi Jia,
As per your script there are two columns having same names (A Award Date , Year) in two different tables.
Your script
Table1: Calendar:
A Award Date A Award Date
Year Year
Because of this it shows synthetic key. to handle this simply comment or remove Year column from the Table 1.
Thanks,
Ishan
HI Jayseelan,
Looks like I do not need the fact table at all. does that mean I could just delete it?
If I delete it, where should I resident the MinMax Table?
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,
Hi Kindly rename the year as Award_Year
Fact:
load *,
year([A Awarded Date])as Award_Year
resident HTD_AWARD_DATA$;
drop table [HTD_AWARD_DATA$];
and load the data.. synthetic key will be removed
Thanks for everyone's help. Everyone said the same thing is just that I am to dumb to understand. HAHAHAHA!
But I do manage to resolve the syn key.
THANK YOU!