Help in Synthetic Key

[Anonymous]

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

[Anonymous]

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.

[jayaseelan]

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

[rahulpawarb]

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

[ishanbhatt]

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

[Anonymous]

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?

[jayaseelan · Accepted solution]

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,

[priyasawant]

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

[Anonymous]

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!