Eliminating synthetic keys using qualify?

[matthewp]

I'm trying to eliminate synthetic keys from my load script but I can't get it to work and return the same data that it returns with the synthetic keys.

Map:

A:

LOAD [HA Emp No] AS [Employee Number],

    [HA Date From],

    [HA Date To]

FROM

$(vDetailsQVDDirectory)AQVD.qvd

(qvd);

E:

LOAD [CPAL Emp No] AS [Employee Number],

    [CPAL Employee Name] AS [Employee Name],

    [CPAL Organization Code] AS [Organisation Code]

FROM

$(vDetailsQVDDirectory)EQVD.qvd

(qvd);

O:

LOAD [CO Organization Code] AS [Organisation Code]

FROM

$(vDetailsQVDDirectory)OQVD.qvd

(qvd);

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min([HA Date From]) as minDate,

max([HA Date To]) as maxDate

Resident A;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD $(varMinDate) + Iterno()-1 As Num,

    Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load TempDate AS ADate,

    week(TempDate) As Week,

    Year(TempDate) As Year,

    Month(TempDate) As Month,

    Day(TempDate) As Day,

      WeekDay(TempDate) As DayName,

    YeartoDate(TempDate)*-1 as CurYTDFlag,

    YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

    inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

    date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

    ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

    Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

    WeekDay(TempDate) as WeekDay

Resident TempCalendar

Where  Year(TempDate) = Year(Today())+1

Order By TempDate ASC;

Drop Table TempCalendar;

Join(MasterCalendar)

LOAD DISTINCT [Employee Number]

Resident A;

FinalMasterCalendar:

LOAD *,

  Floor(ADate)&[Employee Number] as Key

Resident MasterCalendar;

DROP Table MasterCalendar;

Join(A)

IntervalMatch(ADate, [Employee Number])

LOAD [HA Date From],

    [HA Date To],

    [Employee Number]

Resident A

;

H:

LOAD [EMP_NO] AS [Employee Number],

    [YEAR] AS Year

FROM

$(vDetailsQVDDirectory)HQVD.qvd

(qvd)

WHERE [YEAR] = Year(Today())+1;

[sunny_talwar · Accepted solution]

You want to eliminate synthetic keys or are you looking to get the right connections because the data isn't coming the right way? I am slightly confused here

[Colin-Albert]

Firstly IntervalMatch will always create a synthetic key by default.

According to Henric Cronstrom, you do not need to worry about synthetic keys created by Interval Match. I bow to his greater wisdom on this point 🙂

The easiest way to eliminate synthetic keys elsewhere is to deal with them one at a time by adding the extra tables to your model one-by-one rather than loading everything and then sorting it out at the end.

I find the easiest way to do this is to move an "exit script" command through the load script after each table has loaded.

First load tables 1 & 2 with an exit script after table 2. Fix any synthetic keys caused by adding table 2.

Now load tables 1, 2 & 3 with an exit script after table 3. Fix any synthetic keys caused by adding table 3.

etc.

This makes is easier to see the cause of any synthetic keys. Often you just need to rename a single field (or delete a field)  rather than qualifying everything to fix the synthetic key.

The same approach can be used to resolve loops.

Finally search for "exit script" to ensure you have not left an exit command in your script that was used for testing!

[Anil_Babu_Samineni]

Perhaps This

A:

LOAD [HA Emp No] AS [Employee Number],

    [HA Date From],

    [HA Date To]

FROM

$(vDetailsQVDDirectory)AQVD.qvd

(qvd);

E:

LOAD [CPAL Emp No] AS [Employee Number1],

    [CPAL Employee Name] AS [Employee Name],

    [CPAL Organization Code] AS [Organisation Code]

FROM

$(vDetailsQVDDirectory)EQVD.qvd

(qvd);

O:

LOAD [CO Organization Code] AS [Organisation Code]

FROM

$(vDetailsQVDDirectory)OQVD.qvd

(qvd);

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min([HA Date From]) as minDate,

max([HA Date To]) as maxDate

Resident A;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD $(varMinDate) + Iterno()-1 As Num,

    Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load TempDate AS ADate1,

    week(TempDate) As Week,

    Year(TempDate) As Year,

    Month(TempDate) As Month,

    Day(TempDate) As Day,

      WeekDay(TempDate) As DayName,

    YeartoDate(TempDate)*-1 as CurYTDFlag,

    YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

    inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

    date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

    ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

    Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

    WeekDay(TempDate) as WeekDay

Resident TempCalendar

Where  Year(TempDate) = Year(Today())+1

Order By TempDate ASC;

Drop Table TempCalendar;

Join(MasterCalendar)

LOAD DISTINCT [Employee Number]

Resident A;

FinalMasterCalendar:

LOAD *,

  Floor(ADate)&[Employee Number] as Key

Resident MasterCalendar;

DROP Table MasterCalendar;

Join(A)

IntervalMatch(ADate, [Employee Number])

LOAD [HA Date From],

    [HA Date To],

    [Employee Number]

Resident A

;

H:

LOAD [EMP_NO] AS [Employee Number2],

    [YEAR] AS Year1

FROM

$(vDetailsQVDDirectory)HQVD.qvd

(qvd)

WHERE [YEAR] = Year(Today())+1;