Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Handling multiple fact table using a composite Key

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 

 

veena123456_1-1591127971195.png

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 

 

 

 

 

 

 

 

 

 

 

 

 
 
 

 

 

 

 

 

0 Replies