4 Replies Latest reply: Dec 5, 2017 5:01 PM by John Cavoulas RSS

    Table not being correlated properly

    Diwas Karki

      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.

      Inactive_AutoFireUsers.pngaccess request report.png

        • Re: Table not being correlated properly
          John Cavoulas

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

            • Re: Table not being correlated properly
              Diwas Karki

              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.

                • Re: Table not being correlated properly
                  John Cavoulas

                  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

              • Re: Table not being correlated properly
                Anil Babu

                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.