Re: Fact Tables Not Connected Properly / Synthetic... - Qlik Community

Alihan_02 · ‎2025-02-11

[EventsForCalendar_temp]:  // <--- NEUER Tabellenname hier
LOAD 
    subject, 
    start_dateTime, 
    start_timeZone as [EventsForCalendar.start_timeZone], 
    end_dateTime as [EventsForCalendar.end_dateTime], 
    end_timeZone as [EventsForCalendar.end_timeZone], 
    eventId as [EventsForCalendar.eventId], 
    attendees as [EventsForCalendar.attendees], 
    SubField(attendees, ',') AS 'Teilnehmer',
    Lower(SubField(attendees, ',')) AS Key,
    location_displayName as [EventsForCalendar.location_displayName], 
    importance as [EventsForCalendar.importance], 
    body_preview as [EventsForCalendar.body_preview], 
    recurrence as [EventsForCalendar.recurrence], 
    categories as [EventsForCalendar.categories], 
    hasAttachments as [EventsForCalendar.hasAttachments], 
    webLink as [EventsForCalendar.webLink], 
    body_contentType as [EventsForCalendar.body_contentType], 
    body_content as [EventsForCalendar.body_content],
    ConvertToLocalTime([start_dateTime], 'CET', false()) as Local_start_dateTime,
  Date(Floor(ConvertToLocalTime([start_dateTime], 'CET', false())), 'YYYY-MM-DD') AS Startdatum,
    If(Floor(ConvertToLocalTime([start_dateTime], 'CET', false())) < Today(), 1, 0) AS IsBeforeCutoff,
    Time(ConvertToLocalTime([start_dateTime], 'CET', false()), 'hh:mm') AS Startzeit, 
    ConvertToLocalTime([end_dateTime], 'CET', false()) as Local_end_dateTime,
    Date(Floor(ConvertToLocalTime([end_dateTime], 'CET', false())), 'YYYY-MM-DD') AS Enddatum,  
    Time(ConvertToLocalTime([end_dateTime], 'CET', false()), 'hh:mm') AS Endzeit,
	WeekDay(ConvertToLocalTime([start_dateTime], 'CET', false())) AS Debug_WeekDay2,

If([end_dateTime] >= Today(), Dual('Anstehende Termine', 1), Dual('Vergangene Termine', 0)) AS Termine,  // Flag für anstehende Termine

 Trim(SubField(subject, '/', 1)) AS Kunde, 
    Trim(SubField(subject, '/', 2)) AS Ort, 
    Trim(SubField(subject, '/', 3)) AS Kategorie, 
    Trim(SubField(subject, '/', 4)) AS Produktgruppe, 
    // Hier wird das '#' entfernt
    Trim(Replace(SubField(subject, '/', 5), '#', '')) AS Kundennummer,
    // Gleiches auch mit _Key_Kunde da ich kundennummer nicht zugreifen kann, qlik verarbeitet alles in dem moment 
    '1_' 
      & Trim(SubField(subject, '/', 4)) 
      & '_' 
      & Trim(Replace(SubField(subject, '/', 5), '#', '')) 
    AS _Key_Kunde,
    Trim(SubField(subject, '/', 6)) AS Terminart,
    Trim(SubField(subject, '/', 7)) AS Kollektionsquartal,
    Trim(Replace(SubField(subject, '/', 5), '#', '')) & '_' &  Trim(SubField(subject, '/', 4)) AS %Key_Field,
    
    ApplyMap('KategorieMapping', Lower(Trim( Trim(SubField(subject, '/', 3)))), Upper(Trim( Trim(SubField(subject, '/', 3))))) as KategorieStandard
    


WHERE WildMatch(Trim(SubField(subject, '/', 6)), 'S1', 'RT', 'OT')
;  // nach Terminart wenn Struktur vielleicht geändert wird dann auf 6 setzen 


SELECT subject,
	start_dateTime,
	start_timeZone,
	end_dateTime,
	end_timeZone,
	eventId,
	attendees,
	location_displayName,
	importance,
	body_preview,
	recurrence,
	categories,
	hasAttachments,
	webLink,
	body_contentType,
	body_content
FROM EventsForCalendar
WITH PROPERTIES (
WITH PROPERTIES (
calendarID='REDACTED',
maxResults=''
);



LIB CONNECT TO 'MyDatabaseConnection';


[int_ausap]:
LOAD 
    Datum_Anfangstermin, 
    Datum_Auftragseingang, 
    Datum_Storno,  
    Saison,
    Trim(Storno) as Storno,
    Anzahl_Bestellt,
    Anzahl_Fakturiert, 
    Kundennummer as Kundennummer,
    Produktgruppe as Produktgruppe,
    Kundennummer & '_' & Produktgruppe as %Key_Field 
;
// Storno Feld ergänzen, Neue Mapping 
SQL SELECT
    Datum_Anfangstermin,
    Datum_Auftragseingang,
    Datum_Storno,
    Saison,
    Storno,
    SUM(Anzahl_Bestellt)   AS Anzahl_Bestellt,
    SUM(Anzahl_Fakturiert) AS Anzahl_Fakturiert,
    Produktgruppe,
    Kundennummer
FROM my_database.PROD.int_ausap

WHERE CAST(SUBSTR(Saison, 3) AS INT64) >= (EXTRACT(YEAR FROM CURRENT_DATE()) - 2000 - 1)
GROUP BY
    _Key_Kunde,
    Datum_Anfangstermin,
    Datum_Auftragseingang,
    Datum_Storno,
    Saison,
    Storno,
     Kundennummer,
     Produktgruppe
;











[EventsForCalendar]:
Load 
	*,
    If (Produktgruppe = 'alle', 
    ApplyMap('PgMapping', Upper(Trim(KategorieStandard)), (Trim(Produktgruppe))) ,
   Produktgruppe)
    as Produktgruppe_alle

Resident EventsForCalendar_temp;









LinkTable:
Load Distinct
[%Key_Field],
SubField(%Key_Field, '_', 1) AS Kundennummer,  // Extrahiert Kundennummer
    SubField(%Key_Field, '_', 2) AS Original_Produktgruppe,  // Nimmt den ursprünglichen Produktgruppenwert
    SubField(SubField(%Key_Field, '_', 2), ' ') AS Produktgruppe  // Trennt Mehrfachwerte (z.B. "91 92")

Resident EventsForCalendar_temp;
//1 




Concatenate(LinkTable)
Load Distinct 
[%Key_Field],
SubField(%Key_Field, '_', 1) AS Kundennummer,  // Extrahiert Kundennummer
    SubField(%Key_Field, '_', 2) AS Original_Produktgruppe,  // Nimmt den ursprünglichen Produktgruppenwert
    SubField(SubField(%Key_Field, '_', 2), ' ') AS Produktgruppe  // Trennt Mehrfachwerte (z.B. "91 92")  // Trennt Mehrfachwerte,
Resident int_ausap;

















 
 // Falls [EventsForCalendar] diese Felder enthält, kannst du sie entfernen:
Drop Fields %Key_Field From [EventsForCalendar];
Drop Fields %Key_Field From [int_ausap];
//Drop Fields Kundennummer, Produktgruppe From [EventsForCalendar];
//Drop Fields Kundennummer, Produktgruppe From [int_ausap];

Drop Table EventsForCalendar_temp;

[EventsForCalendar_temp]:  // <--- NEUER Tabellenname hier
LOAD 
    subject, 
    start_dateTime, 
    start_timeZone as [EventsForCalendar.start_timeZone], 
    end_dateTime as [EventsForCalendar.end_dateTime], 
    end_timeZone as [EventsForCalendar.end_timeZone], 
    eventId as [EventsForCalendar.eventId], 
    attendees as [EventsForCalendar.attendees], 
    SubField(attendees, ',') AS 'Teilnehmer',
    Lower(SubField(attendees, ',')) AS Key,
    location_displayName as [EventsForCalendar.location_displayName], 
    importance as [EventsForCalendar.importance], 
    body_preview as [EventsForCalendar.body_preview], 
    recurrence as [EventsForCalendar.recurrence], 
    categories as [EventsForCalendar.categories], 
    hasAttachments as [EventsForCalendar.hasAttachments], 
    webLink as [EventsForCalendar.webLink], 
    body_contentType as [EventsForCalendar.body_contentType], 
    body_content as [EventsForCalendar.body_content],
    ConvertToLocalTime([start_dateTime], 'CET', false()) as Local_start_dateTime,
  Date(Floor(ConvertToLocalTime([start_dateTime], 'CET', false())), 'YYYY-MM-DD') AS Startdatum,
    If(Floor(ConvertToLocalTime([start_dateTime], 'CET', false())) < Today(), 1, 0) AS IsBeforeCutoff,
    Time(ConvertToLocalTime([start_dateTime], 'CET', false()), 'hh:mm') AS Startzeit, 
    ConvertToLocalTime([end_dateTime], 'CET', false()) as Local_end_dateTime,
    Date(Floor(ConvertToLocalTime([end_dateTime], 'CET', false())), 'YYYY-MM-DD') AS Enddatum,  
    Time(ConvertToLocalTime([end_dateTime], 'CET', false()), 'hh:mm') AS Endzeit,
	WeekDay(ConvertToLocalTime([start_dateTime], 'CET', false())) AS Debug_WeekDay2,

If([end_dateTime] >= Today(), Dual('Anstehende Termine', 1), Dual('Vergangene Termine', 0)) AS Termine,  // Flag für anstehende Termine

 Trim(SubField(subject, '/', 1)) AS Kunde, 
    Trim(SubField(subject, '/', 2)) AS Ort, 
    Trim(SubField(subject, '/', 3)) AS Kategorie, 
    Trim(SubField(subject, '/', 4)) AS Produktgruppe, 
    // Hier wird das '#' entfernt
    Trim(Replace(SubField(subject, '/', 5), '#', '')) AS Kundennummer,
    // Gleiches auch mit _Key_Kunde da ich kundennummer nicht zugreifen kann, qlik verarbeitet alles in dem moment 
    '1_' 
      & Trim(SubField(subject, '/', 4)) 
      & '_' 
      & Trim(Replace(SubField(subject, '/', 5), '#', '')) 
    AS _Key_Kunde,
    Trim(SubField(subject, '/', 6)) AS Terminart,
    Trim(SubField(subject, '/', 7)) AS Kollektionsquartal,
    Trim(Replace(SubField(subject, '/', 5), '#', '')) & '_' &  Trim(SubField(subject, '/', 4)) AS %Key_Field,
    
    ApplyMap('KategorieMapping', Lower(Trim( Trim(SubField(subject, '/', 3)))), Upper(Trim( Trim(SubField(subject, '/', 3))))) as KategorieStandard
    


WHERE WildMatch(Trim(SubField(subject, '/', 6)), 'S1', 'RT', 'OT')
;  // nach Terminart wenn Struktur vielleicht geändert wird dann auf 6 setzen 


SELECT subject,
	start_dateTime,
	start_timeZone,
	end_dateTime,
	end_timeZone,
	eventId,
	attendees,
	location_displayName,
	importance,
	body_preview,
	recurrence,
	categories,
	hasAttachments,
	webLink,
	body_contentType,
	body_content
FROM EventsForCalendar
WITH PROPERTIES (
calendarID='AQMkADU2ZDljNDViLTJhYjAtNGJkOC04NDU5LTk1OGQzNzY4MjBkYQBGAAADA23JPdsBQEAAsbIr7L7t_ogHAKyLFHDtxs1Ctg-VodHUFiEAAAIBBgAAAMR6ZdMNzCtCueM7xFilhgcAAAIkUAAAAA==',
maxResults=''
);


LIB CONNECT TO 'Developer Space:Google_BigQuery_brax-dwh-377910';

[int_ausap]:
LOAD 
    Datum_Anfangstermin, 
    Datum_Auftragseingang, 
    Datum_Storno,  
    Saison,
    Trim(Storno) as Storno,
    Anzahl_Bestellt,
    Anzahl_Fakturiert, 
    Kundennummer as Kundennummer,
    Produktgruppe as Produktgruppe,
    Kundennummer & '_' & Produktgruppe as %Key_Field 
;
// Storno Feld ergänzen, Neue Mapping 
SQL SELECT
    Datum_Anfangstermin,
    Datum_Auftragseingang,
    Datum_Storno,
    Saison,
    Storno,
    SUM(Anzahl_Bestellt)   AS Anzahl_Bestellt,
    SUM(Anzahl_Fakturiert) AS Anzahl_Fakturiert,
    Produktgruppe,
    Kundennummer
FROM brax-dwh-377910.PROD.int_ausap
WHERE CAST(SUBSTR(Saison, 3) AS INT64) >= (EXTRACT(YEAR FROM CURRENT_DATE()) - 2000 - 1)
GROUP BY
    _Key_Kunde,
    Datum_Anfangstermin,
    Datum_Auftragseingang,
    Datum_Storno,
    Saison,
    Storno,
     Kundennummer,
     Produktgruppe
;











[EventsForCalendar]:
Load 
	*,
    If (Produktgruppe = 'alle', 
    ApplyMap('PgMapping', Upper(Trim(KategorieStandard)), (Trim(Produktgruppe))) ,
   Produktgruppe)
    as Produktgruppe_alle

Resident EventsForCalendar_temp;









LinkTable:
Load Distinct
[%Key_Field],
SubField(%Key_Field, '_', 1) AS Kundennummer,  // Extrahiert Kundennummer
    SubField(%Key_Field, '_', 2) AS Original_Produktgruppe,  // Nimmt den ursprünglichen Produktgruppenwert
    SubField(SubField(%Key_Field, '_', 2), ' ') AS Produktgruppe  // Trennt Mehrfachwerte (z.B. "91 92")

Resident EventsForCalendar_temp;
//1 




Concatenate(LinkTable)
Load Distinct 
[%Key_Field],
SubField(%Key_Field, '_', 1) AS Kundennummer,  // Extrahiert Kundennummer
    SubField(%Key_Field, '_', 2) AS Original_Produktgruppe,  // Nimmt den ursprünglichen Produktgruppenwert
    SubField(SubField(%Key_Field, '_', 2), ' ') AS Produktgruppe  // Trennt Mehrfachwerte (z.B. "91 92")  // Trennt Mehrfachwerte,
Resident int_ausap;

















 
 // Falls [EventsForCalendar] diese Felder enthält, kannst du sie entfernen:
Drop Fields %Key_Field From [EventsForCalendar];
Drop Fields %Key_Field From [int_ausap];
Drop Fields Kundennummer, Produktgruppe From [EventsForCalendar];
Drop Fields Kundennummer, Produktgruppe From [int_ausap];

Drop Table EventsForCalendar_temp;

I am working on a data model in Qlik Sense where I have two fact tables (EventsForCalendar and int_ausap) and want to connect them through a Link Table.

🔎 Goal:

Create a Link Table using %Key_Field (which is a combination of Kundennummer and Produktgruppe).
Ensure that if a Produktgruppe has multiple values, they are split into separate rows.
Avoid synthetic keys.

🚨 Issue:

If I rename Kundennummer and Produktgruppe in the fact tables, I get synthetic keys.
If I drop them from the fact tables, the tables are not properly connected via the Link Table.

SRA · ‎2025-02-11

Hi,

It seems the 2 fact tables have the same key. I would :
- keep a key in each fact table with a different name
- create the link table with the 2 keys + Kundennummer and Producktgruppe.
- remove form fact tables the fields Kundennummer and Producktgruppe.

Model could look like :

It could help to add a %FactType in the link table (useful when you will create your measures in the app)

I hope it will help.
Regards,

Fact Tables Not Connected Properly / Synthetic Keys in Link Table

🔎 Goal:

🚨 Issue:

Data Model

General Question