Removing synthetic keys and circular references for 3 common fields

[billuran]

The goal is to not have any synthetic keys nor circular references, so I need to first join PP_Map to Volume to get the appropriate PP for each service date(this will get rid of circular reference), then get rid of the generated synthetic keys for Charge_BU and Charge_deptid and now PP. Please advise me on how to do this.

Thanks

Here is a bit of my script:

PP_Map:

LOAD `Service_Date`,

    PP;

SQL SELECT `Service_Date`,

    PP

FROM HCOPT.PPE;

Volume:

LOAD `Charged_BU`,

    `Charged_deptid`,

    `Charge Code`,

    `Service Units`,

    `Service_Date`;

SQL SELECT Charged_BU`,

    `Charge Code`,

    `Service Units`,

    `Service_Date`,

    `Charged_deptid`

FROM HCOPT.CDM;

LIB CONNECT TO 'Google Cloud (hcopt_senseadmin)';

Payroll:

LOAD Emplid,

    `Charged_BU`,

    `Charged_deptid`,

    `Hours`,

     PP;

SQL SELECT Emplid,

    Emplid,

    `Charged_BU`,

    `Charged_deptid`,

    `Hours`,

     PP

FROM HCOPT.Payroll;

[sunny_talwar]

Is there a one-to-one relation between PP and Service_Date? In other terms, One Service_Date can only have one PP?

[billuran]

PP means pay period if that helps. so 14 service dates to 1 pay period.

[ger_alegria]

First you can do the left Join betwen PP_Map and Volume, I guess is only a PP value for each Service_Date in PP_Map Table, so you can do the left join without problems.

Then, you can do a link table with fields Charged_deptid and PP, something like this:

Load AutoNumberHash256(Charged_deptid,PP) as %PP_Key,

         Charged_deptid,

         PP,

And make the same Key for the 2 tables.

[sunny_talwar]

May be using ApplyMap:

PP_Map:

Mapping

LOAD `Service_Date`,

    PP;

SQL SELECT `Service_Date`,

    PP

FROM HCOPT.PPE;

Volume:

LOAD `Charged_BU`,

    `Charged_deptid`,

    `Charge Code`,

    `Service Units`,

    `Service_Date`,

   ApplyMap('PP_Map', Service_Date, Null()) as PP;

SQL SELECT Charged_BU`,

    `Charge Code`,

    `Service Units`,

    `Service_Date`,

    `Charged_deptid`

FROM HCOPT.CDM;

LIB CONNECT TO 'Google Cloud (hcopt_senseadmin)';

Payroll:

LOAD Emplid,

    `Charged_BU`,

    `Charged_deptid`,

    `Hours`,

    PP;

SQL SELECT Emplid,

    Emplid,

    `Charged_BU`,

    `Charged_deptid`,

    `Hours`,

    PP

FROM HCOPT.Payroll;

You can now decide if you wish to join the two tables or concatenate them or even create a link table

Concatenate vs Link Table

[billuran]

Thanks to both of you, since now I will have Charged_BU, Charged_deptID, and PP how will a link table work?