Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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