Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Below is my script where I am bringing in data from two qvd tables to create two tables.
SPT_AUDIT_EVENT:
LOAD ID As AuditID,
AuditAutoFireRequestee,
AuditAutoFireOffices,
Completed_Date,
Inactive_AutoFire_Users_Report:
LOAD
SIGNON_ID,
OPERATOR_NAME,
OFFICE_CODE,
LAST_LOGGED_IN_DATE,
(Upper(OFFICE_CODE) & '.' & Upper(SIGNON_ID)) As 'Audit Correlation Key',
FROM
[..\..\QVD\Inactive_AutoFireUsers.qvd](QVD);
"AuditAutoFireRequestee" has same value as "SIGNON_ID" whereas "AuditAutoFireOffices" has the same value as "OFFICE_CODE".
I have two multiboxes in my dashboard named "Access Request Report" and "Inactive_AutoFire_Users". My problem is when I use "Completed_Date" in "Access Request Report" , it brings data but its not brining anything in "Inactive_AutoFire_Users". Below are the image.
I would really appreciate the help. Thanks. Sorry about the image quality. It uploaded like this for some reason.
How are the tables joined? Can you share the data model?
Could be the reason, If you notice with fact and dimensional table doesn't have any primary key to setting up. So, I would prefer to work in single file to show in each of foreign key.
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.
Syntax is key here (pun intended).
In one key you have office code as "1 - ILLINOIS". In the other the office code is like "1- ILLINOIS". Note there is a space between the 1 and the hyphen in the first and not the second.
Also, have you considered separating the multiple office codes like in the first row? Create a separate line for each entry or just use the first entry, or can they be like that?
--john