Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Is there a one-to-one relation between PP and Service_Date? In other terms, One Service_Date can only have one PP?
PP means pay period if that helps. so 14 service dates to 1 pay period.
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.
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
Thanks to both of you, since now I will have Charged_BU, Charged_deptID, and PP how will a link table work?