Table not being correlated properly

[diwaskarki]

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.

[johnca]

How are the tables joined? Can you share the data model?

[Anil_Babu_Samineni]

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.

[diwaskarki]

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.

[johnca]

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