I missed the composite key on the "SPT_AUDIT_EVENT" table.
it looks like this: (UPPER(OFFICES) & '.' & Upper(AuditAutoFireRequestee)) As 'Audit Correlation Key'
OFFICES = OFFICE_CODE & AuditAutoFireRequestee = SIGNON_ID.
The table seems to be correlated, information density is 100%. Subset ratio is 96% on "Inactive_AutoFire_Users_Report" table and 4% on the "SPT_AUDIT_EVENT" table.
The concatenated "Audit Correlation Key" looks like this: "2 - North Coast.DQ4G"
I do have several data like "2 - North Coast.DQ4G" that are similar in both the tables.
But the reason I think my table is not correlating properly is , SPT_AUDIT_EVENT table has a field "Completed_Date", which I am trying to use on a table box. Data in both my "SPT_AUDIT_EVENT" and "Inactive_AutoFire_Users" consists of user alias and offices they have requested access for.
Below is my extract script:
SPT_AUDIT_EVENT:
LOAD ID As AuditID,
MODIFIED As AuditModified,
ACTION As AuditAction,
APPLICATION As AuditApp,
INSTANCE As AuditInstance,
Upper(TARGET & '.' & APPLICATION) As ManagedAttributeAuditKey3,
AuditLCMRequestID,
AuditAutoFireRequestee,
AuditAutoFireRequestID,
AuditAutoFireOffices,
AuditLCMCompletionDate,
Completed_Date,
OFFICES,
(UPPER(OFFICES) & '.' & Upper(AuditAutoFireRequestee)) As 'Audit Correlation Key',
//Timestamp#(Mid(Completed_Date,6,16)&Right(Completed_Date,4),'MMM DD hh:mm:ss YYYY')as Access_Date,
date(date(floor(CREATED/1000/60/60/24))+date('1970-01-01')) as LCMCalendarDate,
TARGET As AuditTarget,
TemplateName,
TemplateName & ' - 1 - Illinois' As BundleTypeKey,
EmailSentTo,
AuditEmailSentSubject,
SOURCE As "Audit Requested By",
TextBetween(ATTRIBUTES, '<entry key="Business Case" value="', '"/>') As "Audit Business Case",
TextBetween(TextBetween(ATTRIBUTES, '<entry key="Approved by RA" value="', '"/>'), '(', ')') As "Audit Approved by RA",
TextBetween(ATTRIBUTES, '<entry key="Completed by (PCU)" value="', '"/>') As "AuditAutoFire Approved by PCU",
TextBetween(ATTRIBUTES, '<entry key="Completed by (DSA)" value="', '"/>') As "AuditAutoFire Approved by DSA",
TextBetween(ATTRIBUTES, '<entry key="Completed by (Manager)" value="', '"/>') As "AuditAutoFire Approved by Manager",
TextBetween(TextBetween(ATTRIBUTES, '<entry key="Approved by Manager" value="', '"/>'), '(', ')') As "Audit Approved by Manager"
//TextBetween(TextBetween(ATTRIBUTES, '<entry key="Requested By" value="', '"/>'), '(', ')') As "Audit Requested By"
FROM [..\..\QVD\SPT_AUDIT_EVENT.qvd] (qvd);
Inactive_AutoFire_Users_Report:
LOAD
REPORT_DATE,
SIGNON_ID,
OPERATOR_INITIAL,
OPERATOR_NAME,
OFFICE_CODE,
JOB_STATION,
LAST_LOGGED_IN_DATE,
INACTIVE_DURATION_IN_DAYS,
(Upper(OFFICE_CODE) & '.' & Upper(SIGNON_ID)) As 'Audit Correlation Key',
INACTIVE_DURATION_IN_MONTHS
FROM
[..\..\QVD\Inactive_AutoFireUsers.qvd](qvd);
I am trying to use the "completed_date" field in a list box which should correlate to fields from "Inactive_AutoFire_Users_Report" table but it is not .(like shown in the pic above) Sorry, if I am being confusing. I will try a different approach if you did not get what I am saying.
