Handling multiple fact table using a composite Key

veena123456 — Sat, 16 Nov 2024 18:33:07 GMT

Hi we have two similar fact table with different source hence i have renamed all my fields as EDW since the data source is edw .Below is my code

FCT_REPEAT_CONTACT_EDW:

LOAD
   *
   , If([Repeat To Agent TMNL ID EDW]='??? Missing'   or
        [Repeat From Agent TMNL ID EDW]='??? Missing' or
        [Repeat Link To Contact Agent FLAG EDW]='No',
         Dual('No', 0), Dual('Yes',1))                      AS [Repeat Load in TPD Dashboard EDW] // 2019-12-17 changed
   , Num(1)                                                 AS [Repeat EDW #]
;
LOAD
     *
   , If(Exists([TEMP_%CONTACT_AGENT_REPEAT_TO_EDW],
                %CONTACT_AGENT_REPEAT_TO_EDW), 'Yes','No')      AS [Repeat Link To Contact Agent FLAG EDW]
;
LOAD
     *
   , Autonumber(
                [Repeat To Agent TMNL ID EDW] &'_'&
                [Repeat To Direction EDW]     &'_'&
                [Repeat To Contact Reason 1 EDW]      &'_'&
               [Repeat To Contact Reason 2 EDW]      &'_'&
             [Repeat To Contact Reason 3 EDW]      &'_'&
                    [Repeat To Date EDW],
                '%CONTACT_AGENT_REPEAT_TO_EDW' )            AS %CONTACT_AGENT_REPEAT_TO_EDW,




   ;
LOAD
     *
   , 'From '& [Repeat From Initial Case Origin EDW]
     &' to '& [Repeat To Initial Case Origin EDW]               AS [Repeat Medium Change EDW]
   ,          [Repeat To Contact Reason 1 EDW] &
      If(Len(Trim([Repeat From Contact Reason 1 EDW]))>0,
          ' / ' & [Repeat From Contact Reason 2 EDW] &
      If(Len(Trim([Repeat From Contact Reason 3 EDW]))>0,
          ' / ' & [Repeat From Contact Reason 3 EDW]))       AS [Repeat From Log Path EDW]
   , [Repeat To Contact Reason 1 EDW] &
      If(Len(Trim([Repeat To Contact Reason 1 EDW]))>0,
          ' / ' & [Repeat To Contact Reason 2 EDW] &
      If(Len(Trim([Repeat To Contact Reason 3 EDW]))>0,
          ' / ' & [Repeat To Contact Reason 3 EDW]))          AS [Repeat To Log Path EDW]
    , If([Repeat Contact Equal Count EDW] = 0, 'No','Yes')     AS [Repeat Equal Log Path EDW]

   ;
LOAD
     [Repeat To Agent TMNL ID EDW]                              AS [Repeat To Agent TMNL ID EDW]
     ,[Repeat From Agent TMNL ID EDW]                           AS [Repeat From Agent TMNL ID EDW]
   ,[Repeat Contact 2 Direction EDW]                            AS [Repeat To Direction EDW]
  , [Repeat Contact 2 Type EDW]                                 AS [Repeat To Type EDW]
, [Repeat To Contact 2 Case Reason 1 EDW]                     AS [Repeat To Contact Reason 1 EDW]
, [Repeat To Contact 2 Case Reason 2 EDW]                     AS [Repeat To Contact Reason 2 EDW]
, [Repeat To Contact 2 Case Reason 3 EDW]                     AS [Repeat To Contact Reason 3 EDW]
, [Repeat To Contact 2 Start Date EDW]                        AS [Repeat To Date EDW]
, [Repeat To Contact 2 Initial Case Origin EDW]               AS [Repeat To Initial Case Origin EDW]

, Num#(Interval([Repeat Contact Interval EDW],'s'))           AS [Repeat Contact Interval EDW]
, [Repeat Contact Count EDW]                                  AS [Repeat Contact Count EDW]
, [Repeat Contact Equal Count EDW]                            AS [Repeat Contact Equal Count EDW]
//, [Repeat Contact Equal Log Path EDW]                         AS [Repeat Contact Equal Log Path EDW]

, [Repeat Contact 1 Direction EDW]                            AS [Repeat From Direction EDW]
, [Repeat Contact 1 Type EDW]                                 AS [Repeat From Type EDW]
, [Repeat From Contact 1 Case Reason 1 EDW]                   AS [Repeat From Contact Reason 1 EDW]
, [Repeat From Contact 1 Case Reason 2 EDW]                   AS [Repeat From Contact Reason 2 EDW]
, [Repeat From Contact 1 Case Reason 3 EDW]                   AS [Repeat From Contact Reason 3 EDW]
, [Repeat From Contact 1 Start Date EDW]                      AS [Repeat From Date EDW]
, [Repeat From Contact 1 Initial Case Origin EDW]             AS [Repeat From Initial Case Origin EDW]
,[Repeat Customer Type EDW]
,[Repeat Segment EDW]
,[Repeat Brand EDW]

FROM [$(v_QVD_DirectoryApplication_RAWData)FCT_REPEAT_CONTACT_EDW.qvd] (qvd)
;

And this is my data model

Using this as composite key %CONTACT_AGENT_REPEAT_TO_EDW i have linked the main fact table (Fact_Conatact_Agent ) I have declared this key in the fact table

using MAP_MISSING
;

FCT_Contact_Agent:
LOAD
*
, %CONTACT_AGENT_REPEAT_TO AS [TEMP_%CONTACT_AGENT_REPEAT_TO] // 2019-11-25 added
, %CONTACT_AGENT_TRANSFER_TO AS [TEMP_%CONTACT_AGENT_TRANSFER_TO]
//,%CONTACT_AGENT_REPEAT_TO_EDW AS [TEMP_%CONTACT_AGENT_REPEAT_TO_EDW]

LOAD
*

, Autonumber(
[Contact Agent TMNL ID] &'_'&
[Contact Direction] &'_'&
[Contact Reason 1] &'_'&
[Contact Reason 2] &'_'&
[Contact Reason 3] &'_'&
[Contact Date]
,'%CONTACT_AGENT_REPEAT_TO_EDW') AS %CONTACT_AGENT_REPEAT_TO_EDW
// 2019-1125 added

Using this i have declared in rest of the fact table how ever in the transform layer the front end is supposed to show

Yes & No but i get No which is incorrect that means there is no relation happening even though after linking a composite key please help

topic Handling multiple fact table using a composite Key in QlikView

Handling multiple fact table using a composite Key